Easysoft ODBC-SQL Server Driver User's Guide - Configuration

Driver = value

The name of the ODBC driver to use with this data source. To connect to a SQL Server 2000 or later instance over an encrypted connection, set this attribute value to Easysoft ODBC-SQL Server SSL. Otherwise, set this attribute value to Easysoft ODBC-SQL Server.

Description = value

A single line of descriptive text that may be retrieved by some applications to describe the data source.

Server = value

The SQL Server instance that you want to connect to. To connect to the default SQL Server instance, type:

machinename

where machinename is the name or IP address of the host where SQL Server is running.

Note that if you are connecting to a SQL Server 2005 or later instance that is listening on an IPv6 address, set the IPv6 attribute to 1.

To connect to a named instance, type:

machinename\instancename

where instancename is the SQL Server instance.

To connect to the default SQL Server Express named instance, type:

machinename\sqlexpress

On Windows, you also have the option of using a named pipe connection. To use named pipes, use this format for the Server value:

\\localhost\pipe\MSSQL$instance\sql\query

For example:

\\localhost\pipe\MSSQL$sqlexpress\sql\query

Connection Failover

If your SQL Server database is available on more than one SQL Server machine, you can define a primary server for the database and additional fallback database servers. By default, the Easysoft ODBC-SQL Server Driver will try to connect to the first server that you specify. If that server is unavailable the Easysoft ODBC-SQL Server Driver will try to connect to the next server in the list and so on. Use the format:

Server = primaryserver[:port] [, fallbackserver[:port]...]

where:

• primaryserver is the name or IP address of the primary SQL Server machine on which your database is available.
• port is the TCP port on which the instance is listening. If omitted, the driver will try to connect to the instance that is listening on port 1433.
• fallbackserver is the name or IP address of an alternative SQL Server machine on which your database is available.

For example:

Server = sqlsrvhostA,sqlsrvhostB,sqlsrvhostC:1583

Connection attempts continue until either a connection is successfully made or all the servers in the list have been tried once.

Note that your SQL Server login (as specified by User and Password) needs to be valid on each SQL Server machine in the list. The SQL Server login must have permission to access the database on each SQL Server machine.

If you want to balance the load between database servers, configure the driver to randomly choose the database server it connects to. To do this, set the ClientLB attribute to 1.

Port = num

The TCP port that SQL Server is listening on.

If you are connecting to a default instance that is listening on port 1433, the Port setting can be omitted.

If the SQL Server Browser or the SQL Server 2000 listener service is running, the Easysoft ODBC-SQL Server Driver will automatically detect the port number and the Port setting can be omitted.

By default, named instances of SQL Server use dynamic ports, which means that an available port is assigned when the instance starts. If a SQL Server instance is listening on a dynamically allocated port number, you must omit the Port setting and let the Easysoft ODBC-SQL Server Driver use the browser or listener to detect the port number.

If the SQL Server Browser or listener is not running at your site, your database administrator will have configured each SQL Server instance to listen on a specific TCP port. You need to specify this port with the Port setting.

If your database administrator has hidden the SQL Server instance from the SQL Server Browser or listener, you need to specify the port number of the hidden instance.

If your database administrator has configured the SQL Server instance to listen on multiple ports, use the Port setting to specify the appropriate port number from the available alternatives.

User = value

The SQL Server login name to use when connecting to SQL Server.

If the SQL Server instance uses Windows Authentication (also known as trusted connections), the Windows user name to use to authenticate the connection. Use this format:

domain\username

where:

• domain is the name of the Windows domain that the SQL Server machine is in or one that the SQL Server machine trusts.
• username is the user name of a user who belongs to this domain.

If the SQL Server instance permits SQL Server Authentication, you can also specify a SQL Server user name.

To specify the login name in the connection string, use UID rather than User. For more information about specifying Easysoft ODBC-SQL Server Driver attributes in the connection string, see DSN-less Connections.

Windows If you want to access SQL Server with a Windows log in, choose With Integrated Windows Authentication.

Password = value

The password for the login name specified by User.

To specify the login password in the connection string, use PWD rather than Password.

Authentication = value

Controls the authentication mode used by the Easysoft ODBC-SQL Server Driver. Currently, the only value for this attribute is ActiveDirectoryPassword, which you need to set if you are connecting to SQL Azure with an Azure Active Directory account user name and password.

ServerName = value

This attribute is only relevant if you are using SQL Azure and lets you specify the fully qualified domain name (FQDN) of the SQL Azure server that you want to connect to. For example:

xyz12345yzx.database.windows.net

There are two ways to specify the SQL Azure server, as shown by the following data source extracts:

ServerName = xyz12345yzx.database.windows.net

User = myuser

¯ OR ¯

Server = xyz12345yzx.database.windows.net

User = myuser@xyz12345yzx

For more information about SQL Azure, see the following Easysoft tutorial:

http://www.easysoft.com/products/data_access/odbc-sql-azure-driver/linux-unix.html

Database = value

The default database to use for the connection.

If you omit this attribute, the connection uses the default database defined for the login in SQL Server. The default database for users who do not have their own SQL Server login depends on the local group on the SQL Server machine that they belong to. The default database for members of the local Administrators group is the one defined for the BUILTIN\Administrators login. The default database for members of the local Users group is the one defined for the BUILTIN\Users login (SQL Server Express Edition only).

If the database does not exist or the login does not have permission to access the database, the connection will fail.

Note that using the default database for the login ID is more efficient than specifying a default database in the ODBC data source.

QuotedId = Yes | No

When ON (set to Yes), QUOTED_IDENTIFIERS is set to ON for the connection. SQL Server will then follow the SQL-92 rules regarding the use of quotation marks in SQL Statements. Double quotes can only be used for identifiers, such as column and table names. Character strings must be enclosed in single quotes:

SELECT CompanyName

FROM "Customer and Suppliers by City"

WHERE City = 'New York'

If a single quotation mark is part of the literal string, it can be represented by two single quotation marks.

When OFF, QUOTED_IDENTIFIERS is set to OFF for the connection. SQL Server then follows the legacy Transact-SQL rules regarding the use of quotation marks. Identifiers cannot be quoted and must follow all Transact-SQL rules for identifiers. Literals can be delimited by either single or double quotation marks.

For more information about the QUOTED_IDENTIFIERS option, see the SQL Server Transact-SQL documentation.

By default, QuotedId is ON.

User_Domain = value

If your user name contains a domain, specify the domain with this attribute. So, myuser@mydomain becomes:

User = myuser

User_Domain = mydomain

Omit the @ symbol.

Some applications (for example Oracle Heterogeneous Services) do not support the use of domains in user names.

AnsiNPW = Yes | No

When ON (set to Yes), the ANSI_NULLS, ANSI_WARNINGS, and ANSI_PADDING options are set to ON for the connection.

When ANSI_NULLS is ON, SQL Server enforces ANSI rules for handling NULL comparisons. The ANSI syntax IS NULL or IS NOT NULL must be used for all NULL comparisons. For example:

SELECT *

FROM MyTable

WHERE MyColumn IS NULL

The Transact-SQL syntax = NULL and <> NULL are not supported.

When ANSI_NULLS is OFF, the Equals (=) and Not Equal To (<>) comparison operators must be used to make comparisons with NULL and nonnull values in a table.

When ANSI_WARNINGS is ON, SQL Server generates warning messages for conditions that violate ANSI rules but do not violate the rules of Transact-SQL. For example, SQL Server will generate error and warning messages for divide-by-zero errors, string too large for database column errors and when NULL values are encountered when using aggregate functions. When SET ANSI_WARNINGS is OFF, these errors and warnings are not raised.

When ANSI_PADDING is ON, trailing blanks on varchar values and trailing zeroes on varbinary values are not automatically trimmed.

For more information about the ANSI_NULLS, ANSI_WARNINGS, and ANSI_PADDING options, see the SQL Server Transact-SQL documentation.

By default, AnsiNPW is ON.

Language = value

The national language to use for SQL Server system messages. Use this format:

Language = language

where language is one the language aliases contained in the sys.syslanguages table.

For example:

Language = French

If no language is specified, the connection uses the default language specified for the login on the server.

Appname = value

The name SQL Server uses to identify the application that connects using this data source. For example, the following entry identifies an application as isql:

Appname = isql

The default value is ODBC.

SQL Server stores the application name in the master.dbo.sysprocesses column program_name. The name is returned by the APP_NAME function.

MARS_Connection = Yes | No

When ON (set to Yes), multiple active result sets (MARS) are enabled on the connection if the server is SQL Server 2005 or later. MARS allows applications to have more than one pending request per connection, and in particular, to have more than one active default result set per connection. Applications can execute other statements (for example, INSERT, UPDATE, DELETE, and stored procedure calls) while result sets are open. For example, an application might retrieve unprocessed items from an Orders table and then, while looping through the active result set, use an UPDATE statement to mark each order as processed.

For non-MARS connections (MARS_Connection turned OFF) and earlier versions of SQL Server, applications cannot maintain multiple active statements on a connection. Applications that attempt to do this fail with the error "connection is busy with results of another hstmt". The application has to process or cancel all result sets from one batch before it can execute any other batch on that connection. Note that server-side cursors can be used to work around this limitation. There is a performance penalty associated with server-side cursors however.

For more information about MARS, see the Microsoft article Multiple Active Result Sets (MARS) in SQL Server 2005.

By default, MARS_Connection is OFF.

Logging = value

Whether Easysoft ODBC-SQL Server Driver logging is enabled. To enable Easysoft ODBC-SQL Server Driver logging, add a Logging=Yes entry to the relevant DSN section of the odbc.ini file.

LogFile = value

Use the LogFile attribute to specify the Easysoft ODBC-SQL Server Driver log file name and location. Ensure that the user who is running the application to be traced has write permission to the log file (and to the directory containing it).

PreserveCursor = Yes | No

When ON (set to Yes), the Easysoft ODBC-SQL Server Driver preserves cursors when SQLEndTran commits or rolls back a transaction.

By default, PreserveCursor is OFF, which means that cursors are closed when a transaction is committed or rolled back by using SQLEndTran.

This behaviour can also be configured by setting SQL_COPT_SS_PRESERVE_CURSORS with SQLSetConnectAttr. For more information and a code sample, see SQL_COPT_SS_PRESERVE_CURSORS.

Wsid = value

The workstation ID. The default value is the host name of the machine where the ODBC application is running. SQL Server stores the workstation ID in the master.dbo.sysprocesses column hostname. The ID is returned by sp_who and the HOST_NAME function.

Version7 = Yes | No

Set to Version7 to Yes if you are connecting to a SQL Server 7.0 database.

When initiating the connection, the Easysoft ODBC-SQL Server Driver tries to discover the version of the SQL Server instance. Setting Version7 to Yes reduces the number of steps in the discovery process for SQL Server 7.0 databases. This results in a slightly quicker connection time.

By default, Version7 is OFF (set to No).

ForceShiloh = Yes | No

When ON (set to Yes), the Easysoft ODBC-SQL Server Driver assumes that it is connecting to a SQL Server 2000 instance and only uses the SQL Server 2000 version of TDS to communicate with the instance.

By default, ForceShiloh is OFF (set to No).

ClientLB = Yes | No

Whether the Easysoft ODBC-SQL Server Driver tries to balance the load between the servers specified by the Server setting. The ClientLB setting only has an effect if you specify a primary server and additional fallback servers with Server.

When ClientLB is ON (set to Yes), the Easysoft ODBC-SQL Server Driver randomly selects a server to connect to. If the server is unavailable, the Easysoft ODBC-SQL Server Driver then moves sequentially through the list of other servers.

When ClientLB OFF (set to No, the default), the Easysoft ODBC-SQL Server Driver tries to connect to the servers in the order that they are defined in. (Primary server first and then each additional fallback server.)

Example

You specify a primary server (sqlsrvhostA) and two fallback servers (sqlsrvhostB and sqlsrvhostC):

Server = sqlsrvhostA,sqlsrvhostB,sqlsrvhostC:1583

When ClientLB is ON, the Easysoft ODBC-SQL Server Driver will randomly choose a server to connect to. If, for example, the driver tries to connect to sqlsrvhostB first, it will then try to connect to sqlsrvhostC (if sqlsrvhostB is unavailable) and sqlsrvhostA (if sqlsrvhostC is unavailable).

When ClientLB is OFF, the Easysoft driver will try to connect to sqlsrvhostA and then sqlsrvhostB (if sqlsrvhostA is unavailable) and finally sqlsrvhostC (if sqlsrvhostB is unavailable).

Failover_Partner = value

Use Failover_Partner to specify the current mirror database server. If the initial connection to the principal database server fails, the Easysoft ODBC-SQL Server Driver will attempt a connection to the server specified by Failover_Partner.

For more information about database mirroring, see Database Mirroring.

MultiSubnetFailover = Yes | No

Set MultiSubnetFailover to Yes when connecting to a SQL Server Failover Cluster Instance or the availability group listener of a SQL Server availability group. Setting MultiSubnetFailover to Yes provides faster detection of and connection to the (currently) active server.

By default, MultiSubnetFailover is OFF (set to No).

ApplicationIntent = ReadOnly | ReadWrite

Specifies the application workload type when connecting to a SQL Server Failover Cluster Instance or the availability group listener of a SQL Server availability group. The default is ReadWrite.

ConnectRetryCount = num

Specifies the number of reconnection attempts if there is a connection failure. Valid values range from 0 to 255. Zero (0) means do not attempt to reconnect. The default value is one reconnection attempt.

ConnectRetryInterval = num

Specifies the number of seconds between each connection retry attempt. Valid values are 1-60. The default value is 10 seconds,

VarMaxAsLong = Yes | No

When OFF (set to No), the Easysoft ODBC-SQL Server Driver returns a varchar(max) column as a SQL_VARCHAR with a zero length, which means the maximum size is unlimited. Some applications may interpret this to mean that the column size is zero bytes rather than unlimited and allocate a buffer that is too small for the column data. To work around this, try setting VarMaxAsLong to Yes. When ON (set to Yes), the Easysoft ODBC-SQL Server Driver returns a varchar(max) column as a SQL_LONGVARCHAR.

By default, VarMaxAsLong is OFF.

VarMaxAsVarchar = Yes | No

When ON (set to Yes), the Easysoft ODBC-SQL Server Driver maps a varchar(max) column to a varchar(4000) column and an nvarchar(max) column to a nvarchar(4000) column.

By default, VarMaxAsVarchar is OFF.

DisguiseGuid = Yes | No

When ON (set to Yes), the Easysoft ODBC-SQL Server Driver describes the UNIQUEIDENTIFIER data types as CHAR rather than GUID. This is a workaround for applications such as Oracle's HSODBC that do not recognise UNIQUEIDENTIFIER types and therefore fail to return data from tables containing these column types.

By default, DisguiseGuid is OFF (set to No).

DisguiseLong = Yes | No

When ON (set to Yes), the Easysoft ODBC-SQL Server Driver describes IMAGE and TEXT data types as VARBINARY and VARCHAR. This is a workaround for applications such as Oracle's HSODBC that cannot handle IMAGE and TEXT types and therefore fail to return data from tables containing these column types.

By default, DisguiseLong is OFF (set to No).

LimitLong = num

The maximum size in bytes that the Easysoft ODBC-SQL Server Driver returns for image, ntext, text, nvarchar(max), varbinary(max) and varchar(max) columns. Use LimitLong to restrict the size returned by the driver when describing these data types.

Note LimitLong only has an effect on MAX data types if VarMaxAsLong is ON (set to Yes).

By default, LimitLong is OFF (set to No).

DPrec = num

The precision to use when converting float(25-53) data in a result set to a string

If an application specifies a string as the target type for non-character data in a SQLBindCol or SQLGetData call, the Easysoft ODBC-SQL Server Driver converts the data to the target type. Use the FPrec attribute to specify the precision to use when the driver does this conversion for float(25-53) data.

The default precision is 6.

FPrec = num

The precision to use when converting float(1-24) or real data in a result set to a string

If your application specifies a string as the target type for non-character data in a SQLBindCol or SQLGetData call, the Easysoft ODBC-SQL Server Driver converts the data to the target type. Use the FPrec attribute to specify the precision to use when the driver does this conversion for float(1-24) or real data.

The default precision is 6.

Strftime = format

The format to use when converting ftimestamp data in a result set to a string.

If your application specifies a string as the target type for timestamp data in a SQLBindCol or SQLGetData call, the Easysoft ODBC-SQL Server Driver converts the data to the target type. Use the Strftime attribute to specify the format to use when the driver does this conversion for date, datetime, datetime2, datetimeoffset, smalldatetime, and time data.

The Easysoft ODBC-SQL Server Driver uses strftime to do the conversion, and so format should be one of the format strings supported by strftime. For the available format strings, see the strftime(3) man page.

For example, the format string specified in the following line:

STRFTIME = %d %h %Y %T

would produce:

11 May 2011 12:35:29

given this SQL statement:

SELECT CAST('2011-05-11 12:35:29.123' AS datetime)

Strfsize = num

The display size of a column is the maximum number of characters needed to display data in character form. It may be necessary to increase the default display size for timestamp data to accommodate some of the formats that strftime supports.

For example, to set the display size to 32, add the following line to your data source:

STRFSIZE = 32

ConvToUtf = Yes | No

When ON (set to Yes), the Easysoft ODBC-SQL Server Driver converts UCS-2 encoded data to UTF-8 and vice versa. This enables applications running on UTF-8 platforms to work with Unicode data stored in nchar, nvarchar, nvarchar(max) and ntext columns.

SQL Server uses UCS-2 to encode data in nchar, nvarchar, nvarchar(max) and ntext columns. If your application expects UTF-8 encoded data, and is unable to convert data to this encoding scheme, it will be unable to process Unicode data stored in nchar, nvarchar, nvarchar(max) and ntext columns. To work around this, add this line to your ODBC data source.

ConvToUtf = 1

ConvToUtf also affects SQL statement text, metadata (table names and so on), and SQL statement parameters that are bound as a wide type (SQL_WCHAR, SQL_WVARCHAR, SQL_WLONGVARCHAR). For example:

SQLPrepare( hstmt, "INSERT INTO MYNCHARTABLE VALUES (?)", SQL_NTS );

SQLBindParameter( hstmt, 1, SQL_PARAM_INPUT, SQL_C_CHAR, SQL_WCHAR, 100, 0, &cval, sizeof( cval ), &len1 );

By default, ConvToUtf is OFF (set to No).

Example: Retrieving Data

We ran OpenOffice.org 2.0 on Ubuntu from a shell in which the LANG environment was set to en_GB.UTF-8. With ConvToUtf set to No, we connected to a SQL Server data source in OOo Base and ran this SQL:

use Northwind

select CompanyName from Suppliers where SupplierID = 29

SQL Server stores data in the CompanyName column as a UCS-2 encoded nvarchar type.

The results for this query should be:

Forêts d'érables

Instead, we got these results:

For?ts d'?rables

The ? symbols indicate that application was unable to convert the character from the server encoding scheme to the client encoding scheme.

In OOo Writer, we used the Insert > Special Character command to insert ê and é into a new document. We did this to show that OpenOffice.org running on this system and environment was capable of rendering these two characters. We then saved the document as a Text file and ran the following command at the shell prompt:

$ file ooo_chars.txt

ooo_chars.txt: Unicode text, UTF-8

The file command's output indicates that the encoding scheme OpenOffice was using is UTF-8.

We set ConfToUTF to Yes and reconnected to the data source in Base. Running the same query returned the expected results. This is because the Easysoft ODBC-SQL Server Driver converts the UCS-2 encoded data to UTF-8, the encoding OpenOffice expects.

Example: Inserting Data

We created a SQL file named insert-northwind-shipper.sql on a Ubuntu machine:

-- Insert new record into the Northwind shippers table

USE Northwind;

INSERT INTO Shippers (CompanyName, Phone) VALUES (N'♦'> Diamond Shipping', '(11) 555-2167');

SELECT * FROM Shippers;

To create the file, we used the Vi IMproved (vim) text editor from a shell in which the LANG environment was set to en_GB.UTF-8. To insert the ♦ character in vim, we typed CTRL+V u2666. (u+2666 is the Unicode code point for this character.) The N prefix before the INSERT statement value tells SQL Server that the string contains a Unicode character. To confirm that the SQL file was UTF-8 encoded, we ran the file command:

$ file insert-northwind-shipper.sql

insert-northwind-shipper.sql: Unicode text, UTF-8

In the same shell, we used insert-northwind-shipper.sql as an input file to isql:

/usr/local/easysoft/unixODBC/bin/isql -v SQLSERVER_SAMPLE < insert-northwind-shipper.sql

The SQLSERVER_SAMPLE data source connects to a SQL Server instance that serves the Northwind database. In the data source, ConfToUTF was set to Yes. The command's output confirmed that the new record had been successfully inserted and that the Ubuntu machine was capable of rendering the ♦ character:

SQL>--+---------------------+----------------------+

| ID | CompanyName | Phone |

+-----+------------------------+-------------------------+

| 1 | Speedy Express | (503) 555-9831 |

| 2 | United Package | (503) 555-3199 |

| 3 | ♦ Diamond Shipping | (11) 555-2167 |

+------+-----------------------+-------------------------+

ConvWToUtf = Yes | No

When ON (set to Yes), the Easysoft ODBC-SQL Server Driver converts strings passed to Unicode ODBC calls (with suffix "W") to UTF-8. The Easysoft ODBC-SQL Server Driver also converts metadata and result sets returned by Unicode ODBC calls to UTF-8.

By default, ConvWToUtf is OFF (set to No).

SQLServerUTF = Yes | No

When ON (set to Yes), the Easysoft ODBC-SQL Server Driver sets the ConvWToUtf attribute to Yes prior to connecting to the data source. This provides a workaround for applications that pass UTF-8 encoded strings to SQLConnectW, SQLDriverConnectW and SQLBrowseConnectW.

The SQLServerUTF attribute must be specified in a section named ODBC in odbc.ini. For example:

[ODBC]

SQLServerUTF = Yes

By default, SQLServerUTF is OFF (set to No).

UTF8DB = Yes | No

If you have set a UTF-8 collation at instance, database or column level, add this entry to your data source:

UTF8DB = Yes

UTF-8 collations were introduced in SQL Server 2019 and have the suffix UTF8.

Client_CSet = encoding

Specifies the encoding on the Easysoft ODBC-SQL Server Driver machine.

If set, the Easysoft ODBC-SQL Server Driver tries to convert to and from the specified encoding when retrieving and submitting character data. For example, if you have an application that is expecting EUC-JP encoded character data, you would need to set Client_CSet to specify the EUC-JP encoding:

# Convert from EUC-JP when submitting data to SQL Server

# Convert to EUC-JP when retrieving data from SQL Server

Client_CSet = EUC-JP

All character data is affected by Client_CSet, including data stored in char, varchar, text, nchar, nvarchar, nvarchar(max) and ntext columns, metadata (table names and so on) and SQL statement text and parameters.

Use Client_CSet if you experience data loss/corruption when working with character data and your application cannot convert data to the encoding scheme it expects.

The Easysoft ODBC-SQL Server Driver uses a built-in version of iconv to do the conversion. For a list of available encodings for Client_CSet, run this command on the machine where the Easysoft ODBC-SQL Server Driver is installed:

iconv -l

Set Client_CSet to the encoding that corresponds with the LANG environment variable value on the client machine. For example, if LANG was set to en_US.UTF-8 on the client machine, you would set Client_CSet to UTF-8.

If iconv cannot convert a character, the Easysoft ODBC-SQL Server Driver will omit the character and write this entry to the unixODBC or driver log file (assuming logging is enabled):

One or more characters in the input stream could not be converted

Note that if your client machine encoding is UTF-8, you can use Client_CSet as an alternative to ConvToUTF. You do not need to set both data source attributes.

If you specify a Server_CSet value without specifying a Client_CSet value, the Easysoft ODBC-SQL Server Driver uses ISO8859-1 as the client machine encoding.

Server_CSet = encoding

Specifies the SQL Server encoding for non-Unicode character data.

If set, the Easysoft ODBC-SQL Server Driver tries to convert character data from the specified encoding when retrieving SQL Server data stored in char, varchar and text columns. The Easysoft ODBC-SQL Server Driver also tries to convert strings to the specified encoding when binding parameter markers. For example:

INSERT INTO my_table(my_char_col, my_varchar_col, my_text_col) VALUES (?,?,?)

SELECT * FROM my_table WHERE my_varchar_col = ?

Use Server_CSet if you experience data loss/corruption when working with data stored in char, varchar and text columns.

The Easysoft ODBC-SQL Server Driver links to iconv on your machine at run-time to do the conversion.

Set Server_CSet to the iconv encoding that corresponds with the SQL Server code page. To find out the SQL Server code page, run:

SELECT COLLATIONPROPERTY('collation' , 'CodePage') AS CodePage

where collation is the SQL Server database collation, if set, otherwise the SQL Server instance collation.

In the following example, collation would be Cyrillic_General_CI_AS.

SELECT DATABASEPROPERTYEX('MyDatabase', 'Collation') SQLCollation;

SQLCollation

------------

NULL

SELECT SERVERPROPERTY('Collation') SQLCollation;

SQLCollation

------------

Cyrillic_General_CI_AS

For a list of iconv encodings, run this command on the machine where the Easysoft ODBC-SQL Server Driver is installed:

iconv -l

As an example, if the SQL Server collation was Cyrillic_General_CI_AS, the associated code page is 1251, and you would set Server_CSet to WINDOWS-1251.

If you set the Client_CSet attribute without setting the Server_CSet attribute, the Easysoft ODBC-SQL Server Driver uses the ISO8859-1 encoding as the Server_CSet value.

Server_UCSet = encoding

Specifies the SQL Server encoding for character data.

If set, the Easysoft ODBC-SQL Server Driver tries to convert character data from the specified encoding when retrieving SQL Server data stored in nchar, nvarchar and ntext columns. The Easysoft ODBC-SQL Server Driver also tries to convert strings to the specified encoding when binding parameter markers.

The default Unicode encoding is UTF-16le.

Use_LCID = Yes | No

Whether to automatically work out which character set to use based on the SQL Server column or instance Locale ID (LCID).

If set, the Easysoft ODBC-SQL Server Driver tries to convert character data from the character set when retrieving SQL Server data stored in char, varchar and text columns. The Easysoft ODBC-SQL Server Driver also tries to convert strings to the character set when inserting data into char, varchar and text columns.

The ODBC application must bind the character data as a Unicode data type (for example, a SQL_WCHAR).

This feature emulates the Microsoft Native Client ODBC Driver's automatic translation of character data, and, as is the case with the Microsoft driver, not all character sets are supported.

If your character set is not supported, the Client_CSet and Server_CSet attributes provide an alternative conversion mechanism.

By default Use_LCID is OFF (set to No).

LCID = localeid

Sets the ClientLCID the parameter in the TDS login packet. The ClientLCID parameter is described in the TDS protocol specification:

http://download.microsoft.com/download/a/e/6/ae6e4142-aa58-45c6-8dcf-a657e5900cd3/%5BMS-TDS%5D.pdf

Trusted_Domain = value

The Windows domain that the user specified with User belongs to.

If the user belongs to the same domain as the one that the SQL Server machine is in, you can omit Trusted_Domain. The Easysoft ODBC-SQL Server Driver automatically detects the domain in this case.

If you specify a Domain with Trusted_Domain, set Trusted_Connection to Yes and omit the domain from User. For example:

# Windows authentication

User = mylocalmachineuser

Password = mypassword

Trusted_Connection = 1

Trusted_Domain = mymachinename

Trusted_Connection = Yes | No

Whether to use Windows or SQL Server authentication to validate the connection.

NTLMv2 = Yes | No

If you want to use NTLMv2 to authenticate the Windows user specified with User, set set NTLMv2 to Yes. Otherwise, leave NTLMv2 set to its default value No (OFF).

IPv6 = Yes | No

Set IPv6 to Yes when connecting to a SQL Server 2005 or later instance that is listening on an IPv6 address.

By default, IPv6 is OFF (set to No), which means that the Easysoft ODBC-SQL Server Driver assumes that the target SQL Server instance is listening on an IPv4 address.

For more information about IPv6, see Connecting to SQL Server 2005 or Later by Using IPv6.

ConnectionTimeout = num

The number of milliseconds to wait for any request on the connection to complete before returning to the application. After the initial connection to the SQL Server machine has been established, the Easysoft ODBC-SQL Server Driver will wait num milliseconds each time it needs a response from SQL Server. If no response is received from SQL Server before the timeout expires, the Easysoft ODBC-SQL Server Driver returns the error Timeout expired.

The default value 0 means that no connection timeout is applied by the Easysoft ODBC-SQL Server Driver.

A timeout set by calling SQLSetConnectAttr with the SQL_ATTR_CONNECTION_TIMEOUT connection attribute will override ConnectionTimeout.

LogonTimeout = num

The number of milliseconds to wait for a TCP connection to the SQL Server machine to be established before returning to the application. When you define a timeout, the initial connection phase lasts for num milliseconds. If the Easysoft ODBC-SQL Server Driver is unable to connect to the target SQL Server machine before the timeout expires, it returns the message Connection timeout expired. Note that if you specify a named instance in the Server attribute value, the driver returns a different timeout message: Failed to get datagram from socket.

The default value 0 means that no initial connection timeout is applied by the Easysoft ODBC-SQL Server Driver.

The Easysoft ODBC-SQL Server Driver classes the connection phase as obtaining the IP address of the SQL Server machine and connecting to it. This means that if you specify the Server attribute value as a machine name rather than an IP address, your system resolver library will be used (possibly examining /etc/hosts or doing a DNS query). On some operating systems, gethostbyname(), the call used to resolve a machine name into an IP address, cannot be interrupted and the connection timeout will not work. If this is a problem for you, either specify the SQL Server machine as an IP address or tell your resolver library to consult /etc/hosts before DNS and place an entry in /etc/hosts.

A timeout set by calling SQLSetConnectAttr with the SQL_ATTR_LOGIN_TIMEOUT connection attribute will override LogonTimeout.

RcvBuffer = num

The size of the receive buffer for the socket in bytes. Possible values for num are:

0, do not set the receive buffer size, use the system default value.

n, where n is a number greater than 0, set the receive buffer to the specified size by passing n to the setsockopt() function .

By default, the system default receive buffer size is used.

SoKeepalive = Yes | No

Whether to use TCP keepalive probes to verify that an idle connection is still intact.

When ON (set to Yes), keepalive probes are sent, after a period of inactivity, to verify that the connection to the SQL Server machine is still valid. To do this, the Easysoft ODBC-SQL Server Driver sets the SO_KEEPALIVE socket option by using setsockopt(). If no response to the probes is received, the socket is closed.

The duration of the period of inactivity is a system default, and is typically two hours.

By default, SoKeepalive is OFF (set to No).

PacketSize = num

The TDS packet size in bytes that the Easysoft ODBC-SQL Server Driver will request. The specified packet size must be lower than 65536 bytes.

The default packet size is 4096 bytes.

ColumnEncryption = Enabled | Disabled

Set this attribute to Enabled if you want to query or update data held in an Always Encrypted column. You will also need to set the Driver attribute to Easysoft ODBC-SQL Server SSL.

Always Encrypted columns were introduced in SQL Server 2016. For more information, see:

http://www.easysoft.com/blog/sql-server-always-encrypted.html

Allow_C_Comment = Yes | No

SQL comments are nonexecuting text strings used to document or temporarily disable SQL statements. SQL Server supports comments preceded by double hyphens (--) or delimited by forward slash-asterisk character pairs (/* ... */).

By default, the Easysoft ODBC-SQL Server Driver strips single line /* ... */ comments from SQL statements before passing the SQL to SQL Server. For example:

SELECT ContactID, /* FirstName, */ LastName FROM Person.Contact

becomes:

SELECT ContactID, LastName FROM Person.Contact

To preserve single line /* ... */ comments in the SQL that is passed to SQL Server, set the Allow_C_Comment attribute to Yes.

XATimeout = num

Sets the timeout in seconds for an XA transaction created by an xa_open call initiated by the Easysoft ODBC-SQL Server Driver. For more information about the driver's XA support, see the Easysoft blog on the Easysoft web site.

XASTimeout = num

Sets the socket timeout in seconds for an XA transaction created by an xa_open call initiated by the Easysoft ODBC-SQL Server Driver. If not set, the socket timeout defaults to the XATimeout value + 5.

Kerberos = Yes | No

Whether to access a SQL Server instance as a Kerberos service.

When ON (set to Yes), the Easysoft ODBC-SQL Server Driver will attempt to obtain a service ticket for the following Service Principal Name (SPN):

MSSQLSvc/server:port

where:

server is the name or IP address of the SQL Server machine specified with the Server attribute.

port is the port on which the SQL Server instance is listening, which is specified with the Port attribute.

The ServerSPN attribute provides an alternative way to supply a service principal name.

Do not specify a User or Password value in the data source if you set Kerberos to Yes. The Kerberos application kinit must have already been used for authentication on the Easysoft ODBC-SQL Server Driver machine. For more information about kinit and accessing SQL Server as a Kerberos service, see the following Easysoft tutorial:

http://www.easysoft.com/products/data_access/odbc-sql-server-driver/kerberos.html

By default, Kerberos is OFF (set to No), and the Easysoft ODBC-SQL Server Driver will use either SQL Server or Windows authentication (see SQL Server Authentication Modes) to validate a user specified in the data source.

ServerSPN = value

The Service Principal Name (SPN) for a SQL Server instance that has been registered as a Kerberos service.

Your database administrator will have registered an SPN for your SQL Server instance. Contact your database administrator for the SPN value and then specify that value with ServerSPN.

As an alternative to the ServerSPN attribute, you can set the Kerberos attribute to 1 and the Easysoft ODBC-SQL Server Driver will build an Service Principal Name from the Server and Port attribute values.

If the SPN contains an instance name (for example, MSSQLSvc/mysqlservermachine:myinstance), you need to use the ServerSPN attribute rather than the Kerberos attribute.

Do not specify a User or Password value in the data source if you specify a ServerSPN value.

Windows This attribute is labelled SPN.

FailoverServerSPN = value

The SPN for a mirror server instance that has been registered as a Kerberos service. For more information about Database Mirroring see Database Mirroring. For more information about Kerberos and Database Mirroring, see the Easysoft tutorial:

http://www.easysoft.com/products/data_access/odbc-sql-server-driver/kerberos.html

GSSLib = value

The Easysoft ODBC-SQL Server Driver uses libgssapi_krb5.so, the Kerberos GSS-API library, to request service tickets for accessing SQL Server instances. If the Easysoft ODBC-SQL Server Driver is unable to open this library, the connection will fail with the error:

Krb5: failed to open gss lib (libgssapi_krb5.so)

If the Kerberos GSS-API library is not called libgssapi_krb5.so in your GSS-API distribution, use the GSSLIB attribute in your data source to specify the alternative GSS-API library. For example:

GSSLIB = /opt/extension/lib/libgssapi.so

GSSHost = Yes | No

Whether the Easysoft ODBC-SQL Server Driver allows the use of GSS_C_NT_HOSTBASED_SERVICE or GSS_C_NT_USER_NAME as the target principal name.

When ON (set to Yes), the Easysoft ODBC-SQL Server Driver allows the use of GSS_C_NT_HOSTBASED_SERVICE.

By default, GSSHOST is OFF (set to No).

GSSFlag = req_flags

The Easysoft ODBC-SQL Server Driver allows you to pass req_flags to the gss_init_sec_context() function, which is used to initiate a security context for the driver. The Key Distribution Center (KDC) uses this security context to verify the identity of the client. To pass req_flags to gss_init_sec_context(), use the GSSFLAG attribute:

GSSFLAG = req_flags

where req_flags is a bitmask specifying the requested GSS services. To look up the available bitmask values, refer to the gssapi.h header file for the GSS-API distribution on the Easysoft ODBC-SQL Server Driver machine. The driver default GSSFLAG value is 4, which sets the GSS_C_REPLAY_FLAG flag.

As an example, to request credential delegation, set the GSS_C_DELEG_FLAG flag by including this line in your data source:

GSSFLAG = 1

SQLServerUTF = Yes | No

When ON (set to Yes), the ConvWToUtf attribute is set to Yes prior to connecting to the data source. This provides a workaround for applications that pass UTF-8 encoded strings to SQLConnectW, SQLDriverConnectW and SQLBrowseConnectW.

The SQLServerUTF attribute must be specified in a section named ODBC in odbc.ini. For example:

[ODBC]

SQLServerUTF = Yes

By default, SQLServerUTF is OFF (set to No).