Setting ODBC connection attributes without having to write code

Recently, a customer who was using our SQL Server ODBC driver to connect Oracle on Linux to SQL Server, asked whether it was possible to enable the snapshot isolation level from an ODBC data source.

You can enable this isolation level by setting a connection attribute in an SQLSetConnectAttr call. However, this method is not applicable if you don't have access to the application's source code, as was with the case with our Oracle customer.

As an alternative, you can use a mechanism provided by the unixODBC Driver Manager to set connection attributes:

DMConnAttr=[attribute]=value

Note You need 1.9.10+ of the SQL Server ODBC driver to set connection attributes with DMConnAttr.

The relevant attribute and value for snapshot isolation level are contained in the SQL Server header file sqlncli.h, which is contained in the SQL Server ODBC driver distribution:

#define SQL_COPT_SS_BASE            1200
#define SQL_TXN_SS_SNAPSHOT              0x00000020L
#define SQL_COPT_SS_TXN_ISOLATION        (SQL_COPT_SS_BASE+27)

which gives us the following line to add to our ODBC data source:

DMConnAttr=[1227]=\32

1227 is the attribute (1200+27) and 32 is the value (the decimal value of 0x00000020L is 32).

To test that our settings were correct, we:

$ more /etc/odbc.ini

[SQLSERVER_SAMPLE]
Driver=Easysoft ODBC-SQL Server
Server=myserver
Port=50217
Database=adventureworks
User=sa
Password=p455w0rd

$ /usr/local/easysoft/unixODBC/bin/isql.sh -v SQLSERVER_SAMPLE

SQL> SELECT CASE transaction_isolation_level WHEN 1 THEN 'ReadUncommitted'
WHEN 2 THEN 'ReadCommitted' WHEN 3 THEN 'Repeatable'
WHEN 4 THEN 'Serializable' WHEN 5 THEN 'Snapshot' END
FROM sys.dm_exec_sessions where session_id = @@SPID

ReadCommitted

 more /etc/odbc.ini

[SQLSERVER_SAMPLE]
Driver=Easysoft ODBC-SQL Server
Server=myserver
Port=50217
Database=adventureworks
User=sa
Password=p455w0rd
DMConnAttr=[1227]=\32

$ /usr/local/easysoft/unixODBC/bin/isql.sh -v SQLSERVER_SAMPLE

SQL> SELECT CASE transaction_isolation_level WHEN 1 THEN 'ReadUncommitted'
WHEN 2 THEN 'ReadCommitted' WHEN 3 THEN 'Repeatable'
WHEN 4 THEN 'Serializable' WHEN 5 THEN 'Snapshot' END
FROM sys.dm_exec_sessions where session_id = @@SPID

Snapshot