The MS SQL Server Adapter is used to import (insert), export (select) and amend (update) data in tables of a Microsoft SQL Server Database. This Adapter can be used on the READ (export) side as well as on the WRITE (import) side and supports SQLServer versions from SQL Server 7.0 up to the latest SQL Server 2022. Moreover, the SQL Server Adapter also supports Microsoft Azure "Cloud" SQL databases. How to connect to Azure databases is explained in detail below. See Azure SQL configuration.
General tab
Connection and authentication
MS-SQL Server Adapter - database connectionServer name or IP: This is used to specify which database server the SQL Server Adapter should connect to. Either a DNS name or an IP address can be used. An instance name can also be specified if desired. If the SQL Server is operated on a TCP port other than the standard port 1433, the alternative port can be specified at the end, separated by a comma. If an instance name is specified or a different port is used, they must be entered using the following format:
Server name/instance name or server name/instance name, port
Alternative configurations for using Microsoft Azure SQL databases are shown below.
Database: Choose here the name of the SQL Server database that is to be accessed. On clicking the Test connection button the dropdown selection is automatically filled out with the names of databases available on the SQL Server specified.
Authentication Select either Windows (trusted connection) or SQL Server here. This confirms how FlowHeater should login to the SQL Server database.
trusted connection = means FlowHeater logs on to the SQL Server database using the currently logged in Windows user.
SQL Server = means FlowHeater logs on to the SQL Server database with a specific SQL user name and password.
User / Password: If you selected "SQL Server" for the Authentication option, you must enter here the SQL user name and its associated password. Important: The password is only stored for future use if you check the "Save Password" option. All passwords stored are saved in an encrypted form in the Definition file.
Use an SSL encrypted connection: If you check this option, the communication with SQL Server is encrypted via SSL or TLS. This setting is required for Microsoft Azure SQL databases, among others. For more on this see below.
Do not check SSL server certificate: When this option is checked, the SSL certificate used is not checked for validity. This should only be done for local and trusted SQL servers where no official server certificates apply. Never check this option for Microsoft Azure SQL databases.
Common settings
Insert data: When this option is checked, SQL Insert statements are generated.
Ignore existing records: During an import and when this option is checked, records that already exist in the table are ignored.
Empty the table before importing: When this is checked you tell the SqlServer Adapter to empty the contents of the table prior to running the import, effectively deleting all existing rows.
Update data: When this option is checked, SQL Update statements are generated. Note: If both the Insert and Update options are checked, the SqlServer Adapter checks whether an SQL Update or Insert should be generated in each instance, by reference to the PrimaryKey. Tip: If you are certain there is only data to insert then avoid checking the Update option, as this will make the process significantly faster.
Remove records (Delete): This will attempt to delete existing records by reference to the fields of the index “Primary Key”. Note: This option cannot be used together with the INSERT or UPDATE options.
No updates or inserts to data, only generate an SQL script: When this option is checked, it signals the SqlServer Adapter to make no inline changes to the database, but instead to store an SQL script with Insert and/or Update statements. This is useful for testing during development and for subsequent application to the database. If this option is checked you should also check the option below and enter the filename that the SQL statements are to be stored in.
Generate SQL script: This option instructs the SqlServer Adapter to store the change statements (Insert, Update) as an SQL script file with the specified name and path.
Fields / Data types tab
SQL: 
MS-SQL Server Adapter - fields and data typesdata available varies according to the side of the Adapter in use:
On the READ side: here you can choose from Tables, Views and Procedures.
On the WRITE side: only Tables are available.
On the READ side you also have the possibility to enter complex SQL statements in the text field. Table joins must be defined by hand. In the second combo box the tables, views and procedures are listed that are available in the specified SQL Server database.
Fields: When you click the Load Schema button, information is retrieved from the database schema (field names, field sizes, data types, primary key, etc.) for the SQL statement above. The information about the fields is then loaded into the field list to the left of this button.
Note: The fields in the field list can be ordered in any sequence required. Fields that are not required can either be temporarily disabled here (tick removed) or simply deleted.
Field properties: How the properties of Primary Key and Auto Increment for the currently highlighted field are to be interpreted are adjusted. This information is only required on the WRITE side. No changes are needed here generally, since the correct information is usually obtained directly from the schema.
A PrimaryKey field is used in an Update to identify record that possibly exists.
Auto Increment fields are neither assigned nor amended in Insert/Update statements.
Warning: If you make changes here it can result in more than one record being updated with an Update!
Advanced tab
Common properties
MS-SQL Server Adpater - extented propertiesAutomatically strip spaces from the start and end of content: If you check this option an auto trim of whitespace characters will be carried out on STRING data types. This means that any spaces, tabs and line break characters are automatically removed from the leading and trailing parts of strings.
Use TRUNCATE TABLE (=quicker) instead of DELETE FROM: When the option “Empty the table before importing” under the General tab is checked “TRUNCATE TABLE” instead of “DELETE FROM” (=default) is used to empty the table content.
Connection timeout: Timeout in seconds while waiting for connection to be established. If no connection has been made after this period, then the import/export run is aborted.
Command timeout: Timeout in seconds while waiting for an SQL command to complete. By entering a zero value here, effectively disables the option altogether. In that case SQL commands will never timeout and are awaited endlessly. This option makes sense when you select massive data volumes from a database on the READ side and the SQL Server takes a long time to prepare its result.
Use database transactions: This allows you to control how data is imported. According to the default settings SQL Server Adapter uses a single “large” transaction to secure the entire import process. When importing extremely large volumes you may have to adapt transactional behavior to your needs using the “Run AutoCommit after writing every X records”.
Use UNICODE strings: When checked this option will force output of a prefix to the initial apostrophe. e.g. N’import value’ instead of ‘import value’.
Continue running in the face of SQL errors: This option allows you to instruct FlowHeater not to abort a run when it encounters an SQL database error. Warning: This option should only be used in exceptional circumstances, because database inconsistencies could result.
Connection initialization
User-defined SQL instructions for initializing the connection: You can place any user-defined SQL commands here, which will be used for initializing the connection after a connection to SQL Server has been established.
Special Adapter settings
Allow inserts into auto-increment (ID) fields: When you check this option it permits you to write predetermined values into SQL auto-increment fields. Warning: In this case you must also uncheck the “Auto increment” option under the “Fields / Data types” tab for the fields affected.
Transaction Isolation Level: Here you specify the database isolation level to be used for transactions. The following levels are supported: READ COMMITTED, READ UNCOMMITTED, REPEATABLE READ, SNAPSHOT and SERIALIZABLE. The default level is READ COMMITTED. Note: The "Transaction Isolation Level" chosen must be supported by the Microsoft SQL Server you are using, you may have to adjust the configuration of SQL Server first.
Adapter settings in the Format tab
The use of the Format tab is described in detail under general information about the usage of Adapters
Connecting to Microsoft Azure "Cloud" databases
The SQL Server Adapter can also be used to import/export Microsoft Azure databases. All you need is the connection string displayed in the Azure Portal under ADO.NET for the created database.
e.g.: Server=tcp:flowheater.database.windows.net,1433;Initial Catalog=fhtest;Persist Security Info=False;User ID=fhuser;Password={password};MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;
First take the "Server=" part of this string up to the first semicolon and enter this part into “Server name or IP”. In the example this would be "tcp:flowheater.database.windows.net,1433".
Then you need to specify the Azure database to use, this is the "Initial Catalog=" part of the string, in this case it would be "fhtest". Next, the authentication method must be selected as "SQL Server" and the "User ID" and “Password” parts must be entered.
Finally, ensure that SQL Server Adapter will use an encrypted connection to the Azure SQL database. All you need to do is to check "Use an SSL encrypted connection".
Note: For Azure "Cloud" SQL databases, you should always check the validity of the certificates used. Therefore, do not check the option "Do not check SSL server certificate" under any circumstances!
As soon as your IP address has been enabled in the firewall settings of the Azure portal, you are able to access your Microsoft Azure "Cloud" SQL database over the Internet using the SQL Server Adapter.
Examples
- CSV text file import into SQL Server
- Exporting a flat file (report) from MS SQL Server
- SQL Server Import IDENTITY (Auto Increment) fields
- CSV export divided into several files
Microsoft®, Windows®, SQL Server® are registered trademarks of Microsoft Corporation
