-1

Another user is using a database she has created in a python session, lets call it user_db. I, in SSMS, delete a different database, lets call it other_db. After I delete other_db, her scripts claim they are unable to find the db id for user_db. If she drops user_db and recreates it, the issue still exists. If she uses a new name like new_user_db her scripts work again.

To me it seems like something on the unixodbc/freetdbs/pyodbc/python side is caching database IDs. Can anyone enlighten me as to which tools might be causing the issue and possibly a solution for it?

error:

pyodbc.ProgrammingError: ('42000', "[42000] [FreeTDS][SQL Server]Could not find database ID (id_number)

EDIT: Software Versions pyodbc -- 4.0.16 centos -- 6.6 unixODBC -- 2.2.14 freeTDS -- 0.91

Repeatable via: User 1 (In Python):

conn = pyodbc.connect(cxnstring)
cursor = conn.cursor()
cursor.execute('CREATE DATABASE test')
cursor.execute('CREATE DATABASE test_1')
cursor.execute('CREATE TABLE test_1.dbo.test_table (column_1 INT)')
cursor.execute('SELECT * FROM test_1.dbo.test_table')

User 2 (in SSMS):

DROP DATABASE test_1

User 1 (in Python):

conn = pyodbc.connect(cxnstring)
cursor = conn.cursor()
cursor.execute('CREATE DATABASE test_1')
cursor.execute('CREATE TABLE test_1.dbo.test_table (column_1 INT)')
cursor.execute('SELECT * FROM test_1.dbo.test_table')

Edit corrected SQL syntax

Lennon McCartney
  • 1
  • 1
  • 2
    I am unable to reproduce your issue on Ubuntu 16.04 with unixODBC 2.3.1, FreeTDS 0.91, and pyodbc 4.0.17. Please [edit] your question with the versions you are using and a [mcve] that demonstrates the problem. – Gord Thompson Jul 27 '17 at 16:06
  • Try your repro code again after replacing all occurrences of `test_1.test_table` with `test_1..test_table`. It looks like you are mixing up catalog (a.k.a. database) references with schema references in your [qualified object names](https://technet.microsoft.com/en-us/library/ms187879(v=sql.105).aspx). – Gord Thompson Jul 31 '17 at 16:20
  • That would throw a syntax error which is not the error I'm receiving. – Lennon McCartney Aug 01 '17 at 16:50
  • No, `database_name..object_name` is valid syntax. – Gord Thompson Aug 01 '17 at 17:05
  • FWIW, still unable to reproduce the issue using your updated code (with `test_1.dbo.test_table`) using unixODBC 2.3.1, FreeTDS 1.0.48, and pyodbc 4.0.17 on Xubuntu 16.04 x64. – Gord Thompson Aug 01 '17 at 17:59
  • Also, please clarify: In your initial description you talk about deleting "a different database", so should your repro steps actually have `DROP DATABASE test` instead of `DROP DATABASE test_1` ...? – Gord Thompson Aug 01 '17 at 18:19

1 Answers1

0

The issue I ran into was related to querying the Information_Schema after a database had been recently dropped by another connection. Running 'DBCC FREEPROCCACHE' every time one queries the Information_Schema of a db appears to resolve the issue.

Lennon McCartney
  • 1
  • 1