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

Issue #3994 resolved
James Bashforth
created an issue

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)

  1. Michael Bayer repo owner

    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.

  2. James Bashforth 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.

  3. James Bashforth 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
    
  4. Michael Bayer 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)

  5. Michael Bayer repo owner

    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

  6. Michael Bayer 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)
    
  7. Michael Bayer 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

  8. Michael Bayer 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 (cherry picked from commit 2574ca4b215cb06720d3ff4352d87ce87ebdd160)

    → <<cset d869a4d3c39e>>

  9. Michael Bayer 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>>

  10. James Bashforth 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?

  11. Michael Bayer 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.

  12. James Bashforth 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?

  13. Michael Bayer 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()
    # ...
    
  14. Log in to comment