Unable to set the connection attribute SQL_COPT_SS_TXN_ISOLATION

[hjb417]

May you allow us to set the connection attribute SQL_COPT_SS_TXN_ISOLATION. It's needed to set the transaction isolation level to SNAPSHOT.

SQL_ATTR_TXN_ISOLATION can be used to set all other isolation levels except for SQL_TXN_SS_SNAPSHOT. If you want to use snapshot isolation, you must set SQL_TXN_SS_SNAPSHOT through SQL_COPT_SS_TXN_ISOLATION. However, you can retrieve the isolation level by using either SQL_ATTR_TXN_ISOLATION or SQL_COPT_SS_TXN_ISOLATION.

https://docs.microsoft.com/en-us/sql/relational-databases/native-client-odbc-api/sqlsetconnectattr?view=sql-server-ver15

[v-chojas]

You can use the attrs_before parameter of connect() to do this. attrs_before = { 1227: 32 } (Look in msodbcsql.h that comes with the driver for the definition of the constants. These are driver-specific attributes.)

[hjb417]

I'm able to set it for now by specifying the ids

SQL_COPT_SS_TXN_ISOLATION = 1227  # SQL_COPT_SS_BASE+27
SQL_TXN_SS_SNAPSHOT = 32
autocommit = conn.autocommit
conn.autocommit=True
conn.set_attr(SQL_COPT_SS_TXN_ISOLATION, SQL_TXN_SS_SNAPSHOT)
conn.autocommit=autocommit

[gordthompson]

Thanks for the workaround!