SQL Server w/ azure data warehouse: "Catalog view 'dm_exec_sessions' is not supported in this version."
Issue #3994
resolved
I am receiving the following error when connecting to Azure SQL DW. On further investigation it seems the the management view below has a slightly different name in Azure SQL DW. "sys.dm_pdw_exec_sessions" not sure if this is the cause of the issue, but if I run the following query on the SQL DW it returns results
SELECT login_name ,COUNT(session_id) AS session_count
FROM sys.dm_pdw_exec_sessions
GROUP BY login_name;
Error Message sqlalchemy.exc.ProgrammingError: (pyodbc.ProgrammingError) ('42000', "[42000] [Microsoft][ODBC Driver 13 for SQL Server][SQL Server]Catalog view 'dm_exec_sessions' is not supported in this version. (104385) (SQLExecDirectW)")
Comments (21)
-
repo owner
-
reporter
Sorry, my fault. This wasn't the query that was emitted, just the error message. The query was my testing of the issue through management studio. I get the error in Management Studio when trying to run a select against dm_exec_sessions
I am using pyodbc and sqlalchemy to connect through a basic script at this stage.
-
reporter
import datetime import os import time import pyodbc import sqlalchemy #database db_driver= '{ODBC Driver 13 for SQL Server}' db_server = 'REMOVED' db_database = 'REMOVED' db_username = 'REMOVED' db_password = 'REMOVED connection_string = 'mssql+pyodbc://'+db_username+'@'+db_server+':'+ db_password + '@'+db_server+'.database.windows.net:1433/'+db_database+'?driver=ODBC+Driver+13+for+SQL+Server' engine = sqlalchemy.engine.create_engine(connection_string) engine.connect() # throws the error -
repo owner
can you please attach a stack trace (again, this is not a SQLAlchemy bug since we don't emit that SQL but I at least can get a clue what is emitting that SQL)
-
reporter
- attached output.log
Error log
-
repo owner
- changed title to get_isolation_level() can't work w/ Azure SQL Datawarehouse
- changed milestone to 1.1.x
- changed component to mssql
OH. this changes everything because you showed one query but then a different error message with almost the same view name so I did not grep for that one.
-
repo owner
microsoft sucks:
sys.dm_exec_sessions (Transact-SQL) 2017-1-31 7 min to read Contributors
THIS TOPIC APPLIES TO: yesSQL Server (starting with 2008)yesAzure SQL Databaseyes Azure SQL Data Warehouse yesParallel Data Warehouse
very annoying how there are 400 different variants of SQL server that don't even document supporting /not supporting these things
-
repo owner
I don't have access to Azure data warehouse. Please run this patch against SQLAlchemy and confirm for me that this solves the issue:
diff --git a/lib/sqlalchemy/dialects/mssql/base.py b/lib/sqlalchemy/dialects/mssql/base.py index 814fc77..35302ce 100644 --- a/lib/sqlalchemy/dialects/mssql/base.py +++ b/lib/sqlalchemy/dialects/mssql/base.py @@ -1786,21 +1786,33 @@ class MSDialect(default.DefaultDialect): raise NotImplementedError( "Can't fetch isolation level prior to SQL Server 2005") - cursor = connection.cursor() - cursor.execute(""" - SELECT CASE transaction_isolation_level - WHEN 0 THEN NULL - WHEN 1 THEN 'READ UNCOMMITTED' - WHEN 2 THEN 'READ COMMITTED' - WHEN 3 THEN 'REPEATABLE READ' - WHEN 4 THEN 'SERIALIZABLE' - WHEN 5 THEN 'SNAPSHOT' END AS TRANSACTION_ISOLATION_LEVEL - FROM sys.dm_exec_sessions - where session_id = @@SPID - """) - val = cursor.fetchone()[0] - cursor.close() - return val.upper() + for view in ("sys.dm_exec_sessions", "sys.dm_pdw_nodes_exec_sessions"): + try: + cursor = connection.cursor() + cursor.execute(""" + SELECT CASE transaction_isolation_level + WHEN 0 THEN NULL + WHEN 1 THEN 'READ UNCOMMITTED' + WHEN 2 THEN 'READ COMMITTED' + WHEN 3 THEN 'REPEATABLE READ' + WHEN 4 THEN 'SERIALIZABLE' + WHEN 5 THEN 'SNAPSHOT' END AS TRANSACTION_ISOLATION_LEVEL + FROM %s + where session_id = @@SPID + """ % view) + val = cursor.fetchone()[0] + except exc.DBAPIError as err: + continue + else: + return val.upper() + finally: + cursor.close() + + util.warn("Could not fetch transaction isolation level: %s" % err) + raise NotImplementedError( + "Can't fetch isolation level on this particular " + "SQL Server version" + ) def initialize(self, connection): super(MSDialect, self).initialize(connection) -
repo owner
that patch is wrong, this works:
diff --git a/lib/sqlalchemy/dialects/mssql/base.py b/lib/sqlalchemy/dialects/mssql/base.py index 6975754..0f4c13d 100644 --- a/lib/sqlalchemy/dialects/mssql/base.py +++ b/lib/sqlalchemy/dialects/mssql/base.py @@ -1689,21 +1689,36 @@ class MSDialect(default.DefaultDialect): raise NotImplementedError( "Can't fetch isolation level prior to SQL Server 2005") - cursor = connection.cursor() - cursor.execute(""" - SELECT CASE transaction_isolation_level - WHEN 0 THEN NULL - WHEN 1 THEN 'READ UNCOMMITTED' - WHEN 2 THEN 'READ COMMITTED' - WHEN 3 THEN 'REPEATABLE READ' - WHEN 4 THEN 'SERIALIZABLE' - WHEN 5 THEN 'SNAPSHOT' END AS TRANSACTION_ISOLATION_LEVEL - FROM sys.dm_exec_sessions - where session_id = @@SPID - """) - val = cursor.fetchone()[0] - cursor.close() - return val.upper() + views = ("sys.dm_exec_sessions", "sys.dm_pdw_nodes_exec_sessions") + for view in views: + cursor = connection.cursor() + try: + cursor.execute(""" + SELECT CASE transaction_isolation_level + WHEN 0 THEN NULL + WHEN 1 THEN 'READ UNCOMMITTED' + WHEN 2 THEN 'READ COMMITTED' + WHEN 3 THEN 'REPEATABLE READ' + WHEN 4 THEN 'SERIALIZABLE' + WHEN 5 THEN 'SNAPSHOT' END AS TRANSACTION_ISOLATION_LEVEL + FROM %s + where session_id = @@SPID + """ % view) + val = cursor.fetchone()[0] + except self.dbapi.Error as err: + continue + else: + return val.upper() + finally: + cursor.close() + + util.warn( + "Could not fetch transaction isolation level, " + "tried views: %s; final error was: %s" % (views, err)) + raise NotImplementedError( + "Can't fetch isolation level on this particular " + "SQL Server version" + ) def initialize(self, connection): super(MSDialect, self).initialize(connection)will be committed probably today
-
repo owner
- changed status to resolved
Prevent SQL server isolation level from failing
Fixed bug where SQL Server transaction isolation must be fetched from a different view when using Azure data warehouse, the query is now attempted against both views and then a NotImplemented is raised unconditionally if failure continues to provide the best resiliency against future arbitrary API changes in new SQL Server versions.
Change-Id: I621b5089febe8ace136428fa133fde1a7e21cda4 Fixes:
#3994(cherry picked from commit 2574ca4b215cb06720d3ff4352d87ce87ebdd160)→ <<cset d869a4d3c39e>>
-
repo owner
Prevent SQL server isolation level from failing
Fixed bug where SQL Server transaction isolation must be fetched from a different view when using Azure data warehouse, the query is now attempted against both views and then a NotImplemented is raised unconditionally if failure continues to provide the best resiliency against future arbitrary API changes in new SQL Server versions.
Change-Id: I621b5089febe8ace136428fa133fde1a7e21cda4 Fixes:
#3994→ <<cset 2574ca4b215c>>
-
reporter
Thank you for the help and apologies that the original request wasn't clear. I'm getting a new error now. regarding transactions. The code that i am using is the same as above, I've attached the latest log file. Do you want t a new issue creating?
-
reporter
- attached output.log
-
repo owner
- changed title to Azure SQL Datawarehouse basic compatibility
clearly this database behaves very differently so we will need to work through each step one by one. is there a publicly available azure DW instance I can test on? im assuming there's no "express" edition
-
repo owner
- changed status to open
-
repo owner
I can potentially give you workarounds on this one but this is likely a bug in PyODBC. It is legal to call rollback() on a DBAPI connection at any time assuming there's no "autocommit" flag set up.
azure DW seems to be a cloud solution that you can get trial accounts for, I'd need someone to help on this and get me inside a console where I can poke around at this database.
-
repo owner
- changed milestone to 1.x.xx
-
reporter
Yes, it works very differently, unfortunately. Even renaming the dm views with pdw makes little sense to me, but then what can you do. Unfortunately, there isn't an express instance. The price isn't cheap so I doubt this would be an option at the moment. I do however have access to SQL DW internally and am happy to assist with testing to get SQLAlchemy working with this version of SQL Server.
I have been using pyodbc natively and managed to get it working by setting the autocommit=true option. Maybe this is what I need to do for SQLAlchemy? Is there a way to set this option other than just on the execute query?
-
repo owner
I have been using pyodbc natively and managed to get it working by setting the autocommit=true option.
OK these are pretty big hints, feel free to bring these up sooner rather than later :).
Yes we need to do that here. Adding direct "autocommit=True" suppor to the MSSQL dialects is something we will do at some point but for now you get this using an event:
from sqlalchemy import create_engine, event engine = create_engine("mssql+pyodbc://...") @event.listens_for(engine, "connect") def _set_autocommit(dbapi_connection, connection_record): dbapi_connection.autocommit = True # use engine # session = Session(engine) # conn = engine.connect() # ... -
repo owner
- changed title to SQL Server w/ azure data warehouse: "Catalog view 'dm_exec_sessions' is not supported in this version."
-
repo owner
- changed status to resolved
the original reported issue here, to reiterate, has been fixed in 2574ca4b215c and is released as of version 1.1.11. For subsequent issues, please open new issues.
- Log in to comment
that's not a query emitted by SQLAlchemy and also doesn't seem to be in pyodbc either. Perhaps your ODBC driver is doing this, but mostly it looks like the application you're using is trying to emit this query. A stack trace (see bug reporting guidelines) would at least reveal what Python code has triggered this query being emitted but this does not look like a SQLAlchemy bug.