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 via 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
