MSSQL Isolation Level support breaks compatibility with SQL Server 2000

[John Lavelle]

Isolation Level support was added to the MSSQL dialect in commit 4d147c3.
The function here: https://bitbucket.org/zzzeek/sqlalchemy/src/4d147c3b169a3d57c0528b0a2633a30866fcb2f9/lib/sqlalchemy/dialects/mssql/base.py?fileviewer=file-view-default#base.py-1633:1648 fails when attempting to connect to SQL Server 2000, since sys.dm_exec_sessions was not added until 2005.

Is there a workaround to avoid calling this function when connecting?

[Mike Bayer]

no workaround other than monkeypatch at this time. Can you suggest how to get the current isolation level on SQL server 2000 (or what the default value is) ? I can push a fix with this information.

[Mike Bayer]

since "minor", targeting at 1.1.x only.

[John Lavelle]

You could do something like this:

CREATE TABLE #unlikely_temp_name (SetOption varchar(100), Value varchar(100))

INSERT INTO #unlikely_temp_name
EXEC('DBCC USEROPTIONS WITH NO_INFOMSGS')

SELECT UPPER(Value)
FROM #unlikely_temp_name
WHERE SetOption = 'isolation level'

DROP TABLE #unlikely_temp_name

I'm not a SQL Server expert so I don't know if that's the best approach. Unfortunately you can't insert the results of the EXEC statement into a table variable in SQL Server 2000.

[Mike Bayer]

That's not really something we can do when we first connect. I'll google for "SQL server 2000 default isolation level" now.

[Mike Bayer]

can you identify the source of "sys.dm_exec_sessions was not added until 2005."? I basically have to put a version check here but per https://msdn.microsoft.com/en-us/library/ms181509.aspx it's claiming this only goes back to SQL Server 2008.

[Mike Bayer]

anyway, this would be the patch for this:

diff --git a/lib/sqlalchemy/dialects/mssql/base.py b/lib/sqlalchemy/dialects/mssql/base.py
index 3e16e6e..cfb3df5 100644
--- a/lib/sqlalchemy/dialects/mssql/base.py
+++ b/lib/sqlalchemy/dialects/mssql/base.py
@@ -1685,6 +1685,10 @@ class MSDialect(default.DefaultDialect):
         cursor.close()

     def get_isolation_level(self, connection):
+        if self.dialect.server_version_info < MS_2005_VERSION:
+            raise NotImplementedError(
+                "Can't fetch isolation level prior to SQL Server 2005")
+
         cursor = connection.cursor()
         cursor.execute("""
           SELECT CASE transaction_isolation_level

the default implementation will run the function and set the result to None if NotImplementedError is raised. I will commit this but feel free to confirm this fixes your issue.

[John Lavelle]

That appears to work, although it should be self.server_version_info instead of self.dialect.server_version_info. Thanks a lot for the speedy response. SQL Server 2005 definitely has sys.dm_exec_sessions, it is present on my own installation and you can find references to it in old articles.

[Mike Bayer]

Don't check isolation level prior to SQL Server 2005

Added a version check to the "get_isolation_level" feature, which is invoked upon first connect, so that it skips for SQL Server version 2000, as the necessary system view is not available prior to SQL Server 2005.

Change-Id: If4f860513f0aae6625803f449714aedfc5075f57 Fixes: #3898

→ <<cset 3757c0cdfb1c>>

[Mike Bayer]

thanks for getting back to me.