Invalid object name 'My Table Name'

Question

Invalid object name 'My Table Name'

kedric

Old Hand

Points: 323
More actions
May 15, 2009 at 9:35 pm

#221373

The error "Invalid object name 'Table Name'" is coming up when execute a SELECT, INSERT, etc., where Table Name is the name of the table I'm attempting to access. The BUILTIN\Administrators and sa logins are both members of the sysadmin server role. I get the same Thanks in advance to anyone with suggestions.

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

if it literally says 'table name' , with a space, you'll need to wrap any name with spaces in double quotes or brackets:

SELECT * from [My Table Name] WHERE [First Name] = 'Bob'

or

SELECT * from "My Table Name" WHERE "First Name" = 'Bob'

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

kedric Old Hand Points: 323 More actions · Answer 2

Hello. Thanks for your response. The name of my table is users with no spaces.

Lowell SSC Guru Points: 323518 More actions · Answer 3

then it is probably owned by a different schema than dbo, and needs to be identified by that.

what is the real, exact error message?

maybe if you have a user named "webdev", and that user created a table, it needs to be referenced by SELECT * FROM webdev.NewTable, and not just NewTable (which assumes it is owned by dbo.NewTable

do Select * from Information_Schema.Tables and see if the table exists in there, and who the owner is.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

kedric Old Hand Points: 323 More actions · Answer 4

I executed the command: SELECT * FROM Information_Schema.Tables and the users table wasn't in there. The schema for the users table is dbo. Under the object explorer, I right click the table name and click properties, then I add my windows login as a user, check the select grant permission check box. Then when I run the SELECT * FROM users I see the results rendered from my web app, but when I disconnect from SQL Server and run the same query I get the error message:

Msg 208, Level 16, State 1, Line 1

Invalid object name 'users'.

Andy Hogg SSCarpal Tunnel Points: 4585 More actions · Answer 5

What collation are you using?

If you are using a CS (Case Sensitive) collation then you will have to refer to the object using exactly the same case as it appears e.g. Users, USERS, users will all be different.

kedric Old Hand Points: 323 More actions · Answer 6

I'm using the Latin1_General Collation with the Accent-Sensitive option checked.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 7

Does the user that the web app connects as have rights to that table?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

kedric Old Hand Points: 323 More actions · Answer 8

I'm using Windows Authentication and I have the database role of db_owner and own the dbo_owner schema. Is this sufficient?