Microsoft SQL Server

  • Configuring SQL Server Repository

    This article provides information about configuring MS SQL Server Repository connection

    By default MS SQL Server repository connection is using 32 bit Microsoft OLE DB Provider for SQL Server, 
    it is possible to use different 32 bit OLE DB Provider by switching to OLE DB Repository connection type.

    oledb

    To create a new MS SQL Server repository connection

    Click Options
    Click  Plus
    Enter connection name
    Set type to MS SQL Server
    Select/Enter server name
    Enter username and password
    Select database name
    Make sure that connection actually works

     

    MS SQL Server Repository1

    Note: When user name and password are blank windows autentification is used
    To create a new MS SQL Server repository connection using Ole DB

    Click Options
    Click  Plus
    Enter connection name
    Set type to Ole DB
    Select build connection
    Select relevant Ole DB provider and enter necessary parameters
    Make sure that connection actually works

     

    MS SQL Server Repository2

    Notes:

    Using Sql Server user name and password is always better than using windows authentication. Consider the following scenario. Administrator Jonh installs the software everything is working fine. Developer Peter logs in, nothing is working, because the developer has no access to the repository database. Peter gets proper access and he can do his job. Over weekend another administrator want to check execution status he logs in, the same problem happens again.

     

     

     

    Direct link, no registration required.
  • Function sequence error

    Quite often our customers are not able to load data into SQL Server due to "Function sequence error"

    download

    The problem can be easily addressed by installing the latest version of ODBC Drivers for MS SQL Server

    Getting the latest version of SQL Server ODBC Drivers

  • Getting latest version of SQL Server ODBC Drivers

    Both Visual Importer ETL and Advanced ETL Processor are 32-bit applications so they use 32-bit ODBC drivers

    Our software uses the highest driver installed for example If both SQL server 2005 and 2008 are installed it will use SQL Server 2008 Driver.

    To Check Version of SQL Server ODBC Driver do the following

    • Click  Maintain
    • Click ODBC Manager

    donate

    Wiki article with the links to the latest version of drivers.

  • How to enable TLS 1.2 for SQL Server Connections

    Direct link, no registration required.

    Great news, all our ETL products support TLS 1.2 now.

    That does not mean that the software is actually using it.

    To enable it you have to install a lot of patches from Microsoft. they must be installed on both the client and the server.

    https://support.microsoft.com/en-gb/help/3135244/tls-1-2-support-for-microsoft-sql-server

    Then you have to enable TLS 1.2 and disable the rest of the protocols (on the server and client)

    We recommend using IISCripto for it

    https://www.nartac.com/Products/IISCrypto 

    iiscrypto

    And the last step is to reboot the computer

  • Learn how to load data from QVD files

    Advanced ETL Processor Enterprise: Loading QVD file into SQL Server

     

    All our ETL tools have no limitations, which means you can load your data today and solve all the data quality problems right now.

    Direct link, no registration required.
  • Loading Data into MS Sql Server Database

    Load data in Microsoft SQL server in just a few clicks. Plus there is no need to create any scripts or learn command line tools just point and click.

     There several ways to load data into Microsoft SQL Server Database

    • Via ODBC
    • Via OLE DB
    • Using BCP interface

    This article describes loading data via BCP.

    BCP is the fastest way of loading data into SQL Server

    MS SQL Server specific parameters

    Check constraints
    Ensure that any constraints on the destination table are checked during the bulk copy operation. By default, constraints are ignored.

    Keep identity
    Specify that there are values in the data file for an identity column.

    Keep NULLS
    Specify that any columns containing a null value should be retained as null values, even if a default value was specified for that column in the destination table.

    Batch size

    Specify the number of rows in a batch. The default is the entire data file.

    The following values for the Batch sizeproperty have these effects:
    If you set Batch size to zero, the data is loaded in a single batch. The first row that fails will cause the entire load to be cancelled, and the step fails.
    If you set Batch size to one, the data is loaded one row at a time. Each row that fails is counted as a one-row failure. Previously loaded rows are committed.
    If you set Batch size to a value greater than one, the data is loaded one batch at a time. Any row that fails in a batch fails that entire batch; loading stops and the step fails. Rows in previously loaded batches are either committed or if the step has joined the package transaction, provisionally retained in the transaction, subject to later commitment or roll-back.

    Direct link, no registration required.
  • Massive performance boost

    The latest versions of Advanced ETL Processor  and Visual Importer ETL loads data up to two times faster

    Old version:1640 records per second:

    Old Version

     New version:4091 records per second:

    New Version

    Note: Only If your table has TEXT, NTEXT, VARCHAR(MAX), NVARCHAR(MAX), XML, IMAGE or BINARY(MAX) fields. 

    Direct link, no registration required.
  • Transform SQL Server data up to 2 times faster

    A new version of Advanced ETL Processor is available for download

    Changes are:

     + Up to 2 times faster data extraction from SQL Server
     + Up to 2 times faster data extraction from ODBC sources
     + Up to 40 percent faster loading data into SQL Server
     + Up to 40 percent faster loading data into ODBC
     + Up to 10 percent faster QVX files creation
     + Up to 10 percent faster loading data into PostgreSQL
     - Various bugs fixes and improvements

    Here are our test results: pooling data from SQL server

    VersionRecords per secondTime Taken
    5.8.0.19 41,100 1min 12sec
    5.8.0.6 21,436 2min 19sec

    Extract Data From SQL Server NEW Version

     Extract Data From SQL Server OLD Version

    Notes:

    1. The performance also depends on the hardware configuration
    2. Please use our support forum to provide us with feedback
    Direct link, no registration required.
 

This site uses cookies. By continuing to browse the site, you are agreeing to our use of cookies