Troubleshooting error message Invalid Object Name accessing MS SQL with python Using python, I can connect to a MS SQL database, but cannot run any queries at all. I always get the error - invalid object name.
I understand from other stack exchanges that I may be accessing the wrong database (although that is specified in my connection); there may be something wrong in my schema (whatever that is); or I simply don't have permission to run a query (I should have been given read only permissions).
My connection looks like:
import pyodbc

connSurvNet = pyodbc.connect(r'DRIVER={ODBC Driver 17 for SQL Server};' +
   ('SERVER={server},{port};' +
   # 'DATABASE={database};' +
   'UID={username};' +
   'PWD={password}').format(
   server= 'xxx',
   port= 1433,
   database= 'db',
   username= 'xxxx',
   password= 'xxxx')
   )

In another database, I can download a table into a pandas dataframe with the following code:
df = pd.read_sql_query('''SELECT * FROM MyTable''', conn)

But from the database in question, MyTable is never a valid object name.
We have a gui interface for querying the data, which allows me to see the SQL-code used for the queries. It includes code like:
FROM [Data].[Version]

I've a pdf of the tables, with all the table names and columns, but nothing I enter as MyTable is ever valid.
I've run a few ideas from Stack Exchanges, like DB_SELECT(), which shows I'm connected to a database 'master', but I do not know of other things I can do to isolate my issue.
I've tried [Data].[MyTable] as well as just Data.MyTable, and even [Data].[Version], but same error. Looking at the Object Explorer in Management Studio, I can see everything - the server is listed, then in a folder Tables, all the tables are listed, like Data.Version.
I can select the top rows, and see the data. What should I look for that will allow me to put the table into my dataframe? I guess it's obvious, but not to me.
Every table belongs to a schema
you yourself used an example - [Data].[Version]
Data is the Schema and Version is the Object name
So you need to find out which schema MyTable belongs to and supply it in your query.
[Data].[MyTable] for example.
(The Object browser in Management Studio is your friend)
Good to know. Every User has a Default Schema and the default default schema is dbo
Therefore if the Table belongs to the dbo schema and the user has the default default schema it is a shorthand to write MyTable.

The master database is a system database so looks like that might be where your problem is. You could try 3 part naming
dbname.schemaname.tablename
or try running a query &quot;use dbname&quot; which should change your context to the right db
But dig into why you are connecting to master instead of the database you specify in the connection string (might be a type, lack of permissions or many other things)