1

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=***;
)
Gord Thompson
  • 116,920
  • 32
  • 215
  • 418
Hafiz Temuri
  • 3,882
  • 6
  • 41
  • 66
  • 1
    Simple...don't use deprecated views. use sys.databases. this of course has been asked and answered plenty of times. – Sean Lange Jan 23 '18 at 17:32
  • 2
    @SeanLange Is it OP trying to do this or the `pypyodbc` library though? – DavidG Jan 23 '18 at 17:32
  • @SeanLange, please reopen this question as the duplicate question does not address what I am asking. I am using `pyodbc` library as specified by DavidG. – Hafiz Temuri Jan 23 '18 at 17:34
  • 1
    @DavidG that could be I suppose. I sure read this as trying to use the view. I think what you are suggesting is that perhaps the pypyodbc library is trying to view the deprecated view? If that is the case maybe it needs to be updated. – Sean Lange Jan 23 '18 at 17:34
  • @SeanLange Yeah, looks like it does it on connect, nasty. https://stackoverflow.com/questions/45201916/how-to-properly-connect-to-sql-server-in-python-using-pypyodb – DavidG Jan 23 '18 at 17:35
  • Yuck...that sounds like a major issue with that library. That view has been deprecated for close to 15 years now. Surely there is an update by now? – Sean Lange Jan 23 '18 at 17:36
  • 1
    A library shouldn't even try to query the database list on connect anyway! – DavidG Jan 23 '18 at 17:38
  • Is there any workarounds? – Hafiz Temuri Jan 23 '18 at 17:38
  • Are you using the latest version of pypyodbc (1.3.5.2)? And what version of SQL Server are you connecting to? – TT. Jan 23 '18 at 17:40
  • I am using `pyodbc version 1.3.4`, and I couldn't update to `1.3.5.2`. Also, `sql server 13` – Hafiz Temuri Jan 23 '18 at 17:48
  • What does "couldn't update" mean? – Aaron Bertrand Jan 23 '18 at 18:02
  • I have tried `pip install pypyodbc==1.3.5.2`, and it installs `1.3.4` instead. That's why I couldn't update it. – Hafiz Temuri Jan 23 '18 at 18:05
  • I looked for the text `sysdatabases` in the latest version on Github and couldn't find the text. Sadly I don't see a tag for version 1.3.4 for pypyodbc, so if that version uses that deprecated view and you can't upgrade your version, I think you're SOL. – TT. Jan 23 '18 at 18:06
  • I can upgrade it, but I couldn't when I tried. `pip install pypyodbc==1.3.5.2` is installing `1.3.4`. Not sure what to do. – Hafiz Temuri Jan 23 '18 at 18:08
  • @HafizTemuri - The version numbers you cited show that you are indeed using `pypyodbc`. You could try using `pyodbc` instead of `pypyodbc` and see if that helps any. (The current version of `pyodbc` is `4.0.22`.) – Gord Thompson Jan 23 '18 at 19:13
  • `sysdatabases` does exist in Azure. You should post the **full** error message which presumably is `Reference to database and/or server name in 'master..sysdatabases' is not supported in this version of SQL Server.` that three part naming is not supported. – Martin Smith Jan 23 '18 at 19:54

1 Answers1

6

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.

Gord Thompson
  • 116,920
  • 32
  • 215
  • 418