September 23, 2013 at 1:30 pm
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 !!!
ddfg
September 23, 2013 at 1:42 pm
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.
- 😀
September 23, 2013 at 1:55 pm
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 Varsha
September 23, 2013 at 4:05 pm
Thanks for reply guys, but it's NOT schema issue ( i am Sr. DBA i know that part), just wondering what could be wrong ?
ddfg
September 23, 2013 at 4:14 pm
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].
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 Varsha
September 23, 2013 at 4:41 pm
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 ?
ddfg
September 23, 2013 at 4:49 pm
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 Varsha
September 23, 2013 at 6:09 pm
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
September 24, 2013 at 3:06 am
September 24, 2013 at 9:58 am
Do you see the table in SSMS? I you do, what happens when you right-click it and select "top 1000 rows"
Regards,Yelena Varsha
September 24, 2013 at 12:39 pm
Finally !
Shared with you guys that the problem is turned out to be that the table has computed column using a user function , and the new released fuction has problem to run !
Thank you all for input .
ddfg
September 24, 2013 at 12:43 pm
Thank you for sharing with us. Could be a bug or something but nice to know we have the explanation
Regards,Yelena Varsha
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply