I can connect to databases in MS SQL Server Management Studio using my python script without issues (using pyodbc). I then created a database called tempdb - see the db explorer pic referred to below. I did this by running a direct query in MS SQL Management Studio, and created a table (DepartmentTest) Now, in my script if I do:

cursor.execute("SELECT * FROM DepartmentTest")

I get:

[Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object name

Also tried: a few options for the above query such as:

dbo.DepartmentTest
[dbo].DepartmentTest 

(instead of just DepartmentTest as above.)

I don't have this issue when connecting to the master database and accessing the tables in the master database e.g. I can execute:

cursor.execute("SELECT * FROM MSreplication_options")

and I get back the contents. I.e. anything under System Tables works fine with the script.

In the explorer pic referred to below: I can access the tables circled in green. I can't access my table, circled in red.

I assume I am not correctly pointing to my table with the syntax I am using, but I'm not sure how to modify my query. (it's as though anything under System Tables is fine to access with my code. (I did connect to the correct database name with my code) Thanks and regards

CodePudding user response：

You should double check you’re connected to the right database, which is suppose to be ‘tempdb’. If you do that and try running the query again, it should work.

CodePudding user response：

It seems as though the trusted connection to the server was the problem. Once I did a connection with user and password credentials, I was able to access that database.