Search code examples
sql-serverazure-sql-databasepypyodbc

'master..sysdatabases' is not supported in this version of SQL Server

I am trying to connect my flask application with Azure database and that is when I encounter this error. I can't find anything on it, any help would be appreciated.

pypyodbc.connect(
    Driver={SQL Server};Server=tcp:******.net,1433;Database=***;UID=***;PWD=***;
)
Solution

  • The issue is caused by your combination of

    1. pypyodbc
    2. the ancient ODBC driver named "SQL Server", and
    3. Azure SQL

    When pypyodbc tries to establish the connection via pypyodbc.connect one of the (many!) things it does is try to determine whether you have read-only or read-write access to the database. The ODBC call that it uses is translated by the "SQL Server" ODBC driver as

    select substring('NY',status/1024&1+1,1) from master..sysdatabases where name=DB_NAME()

    which Azure SQL apparently does not like.

    By comparison, a more modern ODBC driver like "ODBC Driver 11 for SQL Server" translates that same ODBC call into

    SELECT CASE DATABASEPROPERTYEX( DB_NAME(), 'Updateability') WHEN 'READ_ONLY' THEN 'Y' ELSE 'N' END

    which may be more palatable to Azure SQL.

    So, you can either switch to a newer ODBC driver, or you can try using pyodbc instead of pypyodbc since pyodbc does not seem to query the RO/RW status of the database when it connects.