7

I have

cursor.execute("select RM_ID FROM Sales.dbo.MARKETING where VERSION = 'SomeVersion'")

which gives me the traceback error:

pyodbc.ProgrammingError: ('42S02', "[42S02] [Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object name 'Sales.dbo.MARKETING'. (208) (SQLExecDirectW)")

I have several other lines of code:

cursor.execute("select RUNDATEEST FROM Sales.dbo.MARKETING where VERSION = 'SomeVersion'")

that are exactly the same except for the column name that give me no error. I'm not sure what my mistake is.

Of note:

  • I have already checked the table for the column name to make sure it exists.
  • I have noticed that this column is the key for this table. Perhaps a different syntax is required for keys?
  • When I execute the query in SQL Server, it runs just fine.
Bryan
  • 17,112
  • 7
  • 57
  • 80
James.Wyst
  • 859
  • 3
  • 8
  • 13
  • 1
    I'm not a python developer...can you debug the `cursor.execute` statement that is failing and inspect the sql connection string/properties? Perhaps it's not connecting the the right db. – Dave Mason Mar 17 '14 at 17:09
  • They are using the three part name which includes the database name so DB context should not matter. However if the user is different they may not have the appropriate rights. – JNK Mar 17 '14 at 17:13
  • @DMason I was recently granted access to only this database on SQL Server, so that's unlikely. The string is: cnxn = pyodbc.connect('DRIVER={SQL Server};SERVER=ARACHNSQL1;DATABASE=SALES;Trusted_Connection=yes') – James.Wyst Mar 17 '14 at 17:27
  • @JNK I will check with the Database Administrator. – James.Wyst Mar 17 '14 at 17:36
  • Ok, so it appears the connection string is set at run time. I see `Trusted_Connection-yes`. This means your app is trying to connect to MSSQL using the windows credentials of the application. Is the app running under your credentials? (It might be running under another user. Impersonation could be at play...) – Dave Mason Mar 17 '14 at 17:36

1 Answers1

4

Try to surround schema and table names with brackets:

[Sales].[dbo].[MARKETING]

Perhaps you need to surround column names in the same way.

toscanelli
  • 1,202
  • 2
  • 17
  • 40
  • Adding square brackets solved this error for me. I'm using ODBC from C# to run SQL against two different databases on the same server via a single ODBC connection. – DigitalDan Aug 06 '20 at 11:57