Easysoft ODBC-SQL Server Driver

How do I enable snapshot isolation level in my ODBC application?

Article:
00966
Last Reviewed:
25th January 2024
Revision:
2

SQL Server 2005 introduced a new transaction isolation level: snapshot. A snapshot transaction does not block updates executed by another transaction and can continue to read (but not update) the version of the data that existed when it started. Snapshot isolation is also called row versioning because SQL Server keeps "versions" of rows that are being changed: the original version and the version being changed.

Snapshot isolation is enabled for a database when the ALLOW_SNAPSHOT_ISOLATION database option is set to ON. For example, to enable snapshot isolation for the pubs sample database:

ALTER DATABASE pubs SET ALLOW_SNAPSHOT_ISOLATION ON

By default, this database option is set to OFF.

The Easysoft ODBC-SQL Server Driver supports snapshot isolation through the SQLSetConnectAttr and SQLGetInfo ODBC API functions.

For snapshot transactions, ODBC applications need to call SQLSetConnectAttr and set the SQL_COPT_SS_TXN_ISOLATION attribute to SQL_TXN_SS_SNAPSHOT. SQL_TXN_SS_SNAPSHOT indicates that the transaction will take place under the snapshot isolation level. For example:

SQLSetConnectAttr(dbc, SQL_COPT_SS_TXN_ISOLATION, (SQLPOINTER *)SQL_TXN_SS_SNAPSHOT, 0);

The SQLGetInfo function supports the SQL_TXN_SS_SNAPSHOT value, which has been added to the SQL_TXN_ISOLATION_OPTION info type.

The SQL_COPT_SS_TXN_ISOLATION and SQL_TXN_SS_SNAPSHOT attributes are Easysoft ODBC-SQL Server Driver-specific ODBC extensions. To use these attributes, ODBC applications need to include the sqlncli.h header file. sqlncli.h is installed in /usr/local/easysoft/sqlserver/include.

Applies To

Knowledge Base Feedback

* Did this content help you?
* Please select one option based on your first choice:

(* Required Fields)