Troubleshooting error message Invalid Object Name accessing MS SQL with python

All we need is an easy explanation of the problem, so here it is.

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};' +
   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.

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

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
or try running a query "use dbname" 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)

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from or, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply