SQL Server w/ azure data warehouse: "Catalog view 'dm_exec_sessions' is not supported in this version."

[sqlalchemy-bot]

Migrated issue, originally created by James Bashforth

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)")

---

Attachments: output.log | output.1.log

[sqlalchemy-bot]

Michael Bayer (@zzzeek) wrote:

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.

[sqlalchemy-bot]

James Bashforth wrote:

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.

[sqlalchemy-bot]

James Bashforth wrote:

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

[sqlalchemy-bot]

Michael Bayer (@zzzeek) wrote:

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)

[sqlalchemy-bot]

James Bashforth wrote:

Error log

[sqlalchemy-bot]

Changes by James Bashforth:

• attached file output.log

[sqlalchemy-bot]

Michael Bayer (@zzzeek) wrote:

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.

[sqlalchemy-bot]

Changes by Michael Bayer (@zzzeek):

• changed title from "Azure SQL Datawarehouse error" to "get_isolation_level() can't work w/ Azure SQL Data"

[sqlalchemy-bot]

Changes by Michael Bayer (@zzzeek):

• added labels: mssql

[sqlalchemy-bot]

Changes by Michael Bayer (@zzzeek):

• set milestone to "1.1.x"

[sqlalchemy-bot]

Michael Bayer (@zzzeek) wrote:

microsoft sucks:

https://docs.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-exec-sessions-transact-sql

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

[sqlalchemy-bot]

Michael Bayer (@zzzeek) wrote:

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)

[sqlalchemy-bot]

Michael Bayer (@zzzeek) wrote:

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

[sqlalchemy-bot]

Michael Bayer (@zzzeek) wrote:

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 2574ca4)

→ d869a4d

[sqlalchemy-bot]

Changes by Michael Bayer (@zzzeek):

• changed status to closed

[sqlalchemy-bot]

Michael Bayer (@zzzeek) wrote:

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

→ 2574ca4

[sqlalchemy-bot]

James Bashforth wrote:

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?

[sqlalchemy-bot]

Changes by James Bashforth:

• attached file output.log

[sqlalchemy-bot]

Michael Bayer (@zzzeek) wrote:

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

[sqlalchemy-bot]

Changes by Michael Bayer (@zzzeek):

• changed title from "get_isolation_level() can't work w/ Azure SQL Data" to "Azure SQL Datawarehouse basic compatibility"

[sqlalchemy-bot]

Changes by Michael Bayer (@zzzeek):

• changed status to reopened

[sqlalchemy-bot]

Michael Bayer (@zzzeek) wrote:

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.

[sqlalchemy-bot]

Changes by Michael Bayer (@zzzeek):

• changed milestone from "1.1.x" to "1.x.xx"

[sqlalchemy-bot]

James Bashforth wrote:

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?

[sqlalchemy-bot]

Michael Bayer (@zzzeek) wrote:

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()
# ...

[sqlalchemy-bot]

Changes by Michael Bayer (@zzzeek):

• changed title from "Azure SQL Datawarehouse basic compatibility" to "SQL Server w/ azure data warehouse: "Catalog view "

[sqlalchemy-bot]

Michael Bayer (@zzzeek) wrote:

the original reported issue here, to reiterate, has been fixed in 2574ca4 and is released as of version 1.1.11. For subsequent issues, please open new issues.

[sqlalchemy-bot]

Changes by Michael Bayer (@zzzeek):

• changed status to closed