Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Invalid object name ??! Expand / Collapse
Author
Message
Posted Monday, September 23, 2013 1:30 PM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Friday, August 1, 2014 4:04 PM
Points: 564, Visits: 348
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 !!!



Post #1497518
Posted Monday, September 23, 2013 1:42 PM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Yesterday @ 3:15 PM
Points: 586, Visits: 6,686
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.




-
Post #1497528
Posted Monday, September 23, 2013 1:55 PM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Monday, September 15, 2014 4:12 PM
Points: 3,475, Visits: 582
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

Post #1497533
Posted Monday, September 23, 2013 4:05 PM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Friday, August 1, 2014 4:04 PM
Points: 564, Visits: 348
Thanks for reply guys, but it's NOT schema issue ( i am Sr. DBA i know that part), just wondering what could be wrong ?


Post #1497579
Posted Monday, September 23, 2013 4:14 PM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Monday, September 15, 2014 4:12 PM
Points: 3,475, Visits: 582
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

Post #1497583
Posted Monday, September 23, 2013 4:41 PM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Friday, August 1, 2014 4:04 PM
Points: 564, Visits: 348
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 ?



Post #1497590
Posted Monday, September 23, 2013 4:49 PM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Monday, September 15, 2014 4:12 PM
Points: 3,475, Visits: 582
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

Post #1497591
Posted Monday, September 23, 2013 6:09 PM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Thursday, September 11, 2014 3:32 PM
Points: 977, Visits: 3,357
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
Post #1497599
Posted Tuesday, September 24, 2013 3:06 AM


SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Today @ 4:44 AM
Points: 80, Visits: 402
sound like object permission issue.

Cheers!
[url=http://coffeeandsql.com/][/url]

Post #1497716
Posted Tuesday, September 24, 2013 9:58 AM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Monday, September 15, 2014 4:12 PM
Points: 3,475, Visits: 582
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

Post #1497954
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse