SQL Query Analyzer Invalid Object Name Error

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

SQL Query Analyzer Invalid Object Name Error

Author

Topic

notalian
Starting Member

8 Posts

Posted - 2007-06-11 : 14:18:54

We have several databases but one is behaving differently in SQL Query Analyzer. Please assume we have selected the correct db in the the pull-down and assume database name is DB1 and table name is Table1

SELECT *
FROM db1.[table1]
works fine.

SELECT *
FROM [table1]
returns "Invalid Object Name Error".

This problem causes some complex queries not to work so I'd like to know if this db has some special setting that requires explicit db name in each query.

We have also tried..
use [db1]
SELECT *
FROM [table1]

It still gives the same error.
Any help would be appreciated.
Thansk

Notalian

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-06-11 : 14:24:08

i find hard to believe that this works fine:
SELECT *
FROM db1.[table1]

if it was

SELECT *
FROM db1..[table1]

then it would work ok.
so i guess that db1 is acctually a schema.

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-06-11 : 14:24:15

quote:
SELECT *
FROM db1.[table1]
works fine.

works fine it means DB1 is a user in the system that owns the table. Normally all objects should be owned by dbo.
So when you do a SELECT * FROM [table1] SQL Server assumes either dbo or the username you are connected with.
So you need to change ownership of the object from DB1 to dbo using sp_changeobjectowner. The naming convention is Server.Database.Owner.Object. DB1.Table => Db1 = owner, Table1 = object.

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

notalian
Starting Member

8 Posts

Posted - 2007-06-11 : 14:36:59

Dinakar,

You are correct. That was the problem.

Thanks a lot for the help. And thanks to Spirit1 as well.

Notalian

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources