- edited description
MSSQL Isolation Level support breaks compatibility with SQL Server 2000
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?
Comments (11)
-
reporter -
repo owner 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.
-
repo owner - changed milestone to 1.x.xx
since "minor", targeting at 1.1.x only.
-
repo owner - changed milestone to 1.1.x
-
reporter 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. -
repo owner That's not really something we can do when we first connect. I'll google for "SQL server 2000 default isolation level" now.
-
repo owner 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.
-
repo owner 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.
-
reporter That appears to work, although it should be
self.server_version_info
instead ofself.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. -
repo owner - changed status to resolved
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>>
-
repo owner thanks for getting back to me.
- Log in to comment