Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Invalid object name ??!


Invalid object name ??!

Author
Message
awu
awu
Mr or Mrs. 500
Mr or Mrs. 500 (564 reputation)Mr or Mrs. 500 (564 reputation)Mr or Mrs. 500 (564 reputation)Mr or Mrs. 500 (564 reputation)Mr or Mrs. 500 (564 reputation)Mr or Mrs. 500 (564 reputation)Mr or Mrs. 500 (564 reputation)Mr or Mrs. 500 (564 reputation)

Group: General Forum Members
Points: 564 Visits: 413
Run:

use xxxx
go
select * from [xxxx].[xxxxxx]

Got:

Msg 208, Level 16, State 1, Line 1
Invalid object name 'xxxxxx'.

i double checked:

1) run it under right database and right table, i run dbcc checktable see 55 rows inside;
2) can run sp_help [xxxx].[xxxxxx] without any issues
3) dbcc checkdb without any errors

Anything looks good, just not able to run simple "SELECT", what else could be wrong ? Please help, it's Production issue. Thanks a lot !!!



Andrew Kernodle
Andrew Kernodle
SSChasing Mays
SSChasing Mays (614 reputation)SSChasing Mays (614 reputation)SSChasing Mays (614 reputation)SSChasing Mays (614 reputation)SSChasing Mays (614 reputation)SSChasing Mays (614 reputation)SSChasing Mays (614 reputation)SSChasing Mays (614 reputation)

Group: General Forum Members
Points: 614 Visits: 8135
It's a bit hard to tell from your description, but I'm assuming that [xxxxxx] is a table. Is this correct? If so, you'll either need to specify the schema after the database name (so it'll come out as TestDB.dbo.TestTable, for example; the schema will be the little prefix before the table name in the database list).

This would be my guess from your description; the fact that the DBCC and sp_help commands worked despite the missing schema specification is quite strange, though. More details will be needed if this doesn't work out.

- :-D
Yelena Varshal
Yelena Varshal
Hall of Fame
Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)

Group: General Forum Members
Points: 3480 Visits: 593
Please check who is the object / schema owner. maybe it is not you.
don't we use the syntax [database name].[schema name].[object name]
Check the security, what rights do you have.


Regards,
Yelena Varshal

awu
awu
Mr or Mrs. 500
Mr or Mrs. 500 (564 reputation)Mr or Mrs. 500 (564 reputation)Mr or Mrs. 500 (564 reputation)Mr or Mrs. 500 (564 reputation)Mr or Mrs. 500 (564 reputation)Mr or Mrs. 500 (564 reputation)Mr or Mrs. 500 (564 reputation)Mr or Mrs. 500 (564 reputation)

Group: General Forum Members
Points: 564 Visits: 413
Thanks for reply guys, but it's NOT schema issue ( i am Sr. DBA i know that part), just wondering what could be wrong ?



Yelena Varshal
Yelena Varshal
Hall of Fame
Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)

Group: General Forum Members
Points: 3480 Visits: 593
Well, even a senior DBA has to add [schema name]
I just reproduced your issue.
When I ran on one of my databases
select top 1 * from [my database].[my table]
I got the identical error message about invalid object
When I ran
select top 1 * from [my database].dbo.[my table]
I got a valid query result.

It may be that you have not decribed the issue correctly and you mean to say, that you used USE [db name]
and then in select you mean to say
[schema name].[table name]
but as presented with XXX you say,
USE XXXX
and then
select .... from XXXX.XXXXX
so in your description the schma name is missing.

Yelena


Regards,
Yelena Varshal

awu
awu
Mr or Mrs. 500
Mr or Mrs. 500 (564 reputation)Mr or Mrs. 500 (564 reputation)Mr or Mrs. 500 (564 reputation)Mr or Mrs. 500 (564 reputation)Mr or Mrs. 500 (564 reputation)Mr or Mrs. 500 (564 reputation)Mr or Mrs. 500 (564 reputation)Mr or Mrs. 500 (564 reputation)

Group: General Forum Members
Points: 564 Visits: 413
use database
go
select * from [schema].[tablename]


i use the above, the weirld part is that it works for all other tables except this one, even i try open it in ssms or try to use ssis to export it, gave me the same error, so, what else you will try from here ?



Yelena Varshal
Yelena Varshal
Hall of Fame
Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)

Group: General Forum Members
Points: 3480 Visits: 593
send us the one line result for this table for this:
select * from sysabjects

Could be also a permission issue but for what? If select would be denied to you for this table, the error message would be different,

Have you tried to specify explicitly the database name in the query? like a 3 part name? You probably did....also: I know you said, this is a correct database, right? But is it a correct server?

I am leaving now, will be online tomorrow

Yelena


Regards,
Yelena Varshal

AndrewSQLDBA
AndrewSQLDBA
Ten Centuries
Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)

Group: General Forum Members
Points: 1008 Visits: 3427
Sounds like the owner of the object that you are using, like dbo.ObjectName, does not actually own the object. Look at the properties of the object to verify who actually owns it.

I had an object once that no one actually owned it. That was one object that had to be dropped and created again with a valid owner

Andrew SQLDBA
Carlaabanes
Carlaabanes
SSC Journeyman
SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)

Group: General Forum Members
Points: 82 Visits: 474
sound like object permission issue.

Cheers! :-)
[url=http://coffeeandsql.com/][/url]
Yelena Varshal
Yelena Varshal
Hall of Fame
Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)

Group: General Forum Members
Points: 3480 Visits: 593
Do you see the table in SSMS? I you do, what happens when you right-click it and select "top 1000 rows"


Regards,
Yelena Varshal

Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search