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
 Data Corruption Issues
 Table SUPPOSEDLY missing from sysobjects

Author  Topic 

mstclair
Starting Member

13 Posts

Posted - 2008-01-18 : 11:03:15
Reposted from 2000 area per suggestion. The site is running SQL 2000 build 2187.

I've got an 'accidental client' (sister company without a DBA at the moment) who has a problem that I've never seen before. They have a table that SQL claims to not exist in sysobjects. But it does exist in sysobjects as a User Table:

quote:
select * from MyTable

Msg 604, Level 21, State 5, Line 1
Could not find row in sysobjects for object ID 55671246 in database 'MyTable'. Run DBCC CHECKTABLE on sysobjects
.

dbcc checkdb ('MyDatabase')

Msg 2501, Level 16, State 1, Line 1
Could not find a table or object named 'MyTable'. Check sysobjects.
Msg 2501, Level 16, State 1, Line 1
Could not find a table or object named 'MyTable2'. Check sysobjects.
Msg 2501, Level 16, State 1, Line 1
Could not find a table or object named 'MyTable3'. Check sysobjects.
Msg 2501, Level 16, State 1, Line 1
Could not find a table or object named 'MyTable4'. Check sysobjects.
Msg 2501, Level 16, State 1, Line 1
Could not find a table or object named 'MyTable5'. Check sysobjects.
DBCC results for 'routingengine_historical'.
DBCC results for 'sysobjects'.
There are 483 rows in 14 pages for object 'sysobjects'.
DBCC results for 'sysindexes'.
There are 310 rows in 21 pages for object 'sysindexes'.
DBCC results for 'syscolumns'.
There are 839 rows in 20 pages for object 'syscolumns'.
DBCC results for 'systypes'.
There are 26 rows in 1 pages for object 'systypes'.
DBCC results for 'syscomments'.
There are 470 rows in 35 pages for object 'syscomments'.
DBCC results for 'sysfiles1'.
There are 3 rows in 1 pages for object 'sysfiles1'.
DBCC results for 'syspermissions'.
There are 145 rows in 1 pages for object 'syspermissions'.
DBCC results for 'sysusers'.
There are 19 rows in 1 pages for object 'sysusers'.
DBCC results for 'sysproperties'.
There are 0 rows in 0 pages for object 'sysproperties'.
DBCC results for 'sysdepends'.
There are 421 rows in 4 pages for object 'sysdepends'.
DBCC results for 'sysreferences'.
There are 4 rows in 1 pages for object 'sysreferences'.
DBCC results for 'sysfulltextcatalogs'.
There are 0 rows in 0 pages for object 'sysfulltextcatalogs'.
DBCC results for 'sysfulltextnotify'.
There are 0 rows in 0 pages for object 'sysfulltextnotify'.
DBCC results for 'sysfilegroups'.
There are 1 rows in 1 pages for object 'sysfilegroups'.
CHECKDB found 0 allocation errors and 0 consistency errors in database 'MyDatabase'.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.


dbcc checktable ('MyTable') with ALL_ERRORMSGS

Msg 2501, Level 16, State 1, Line 1
Could not find a table or object named 'MyTable'. Check sysobjects.


dbcc checktable ('sysobjects') with ALL_ERRORMSGS

DBCC results for 'sysobjects'.
There are 483 rows in 14 pages for object 'sysobjects'.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.



I've fully verified the correct existence of the table and how it links to the object ID by running statements like:

quote:
select * from sysobjects where name = 'MyTable' and type = 'U'
select object_id('MyTable')
select object_name(55671246)



Odd that when selecting data from the table the error refers to the database by the table name. Running checktable on sysobjects shows no errors.

Running sp_help 'MyTable' works and shows the expected results as far as columns, indexes, and such. The table structure is fully visible in Management Studio and can be visually explored. Only when querying data is there an issue.

All tables are owned by dbo.

Ideally I need to recover these tables. If that is impossible, I'd like to remove the tables and cleanly free up all the related data pages.

Any thoughts?

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2008-01-18 : 12:40:09
The message is slighgtly garbled....

Could not find row in sysobjects for object ID 55671246 in database 'MyTable'. Run DBCC CHECKTABLE on sysobjects refers to DATABASE MyTable not a table. Is it possible a column definition is at fault....or have you (badly) edited the real error message?


what does "select * from sysobjects" say? can you see the table in there??

can the table be dropped? and re-loaded from a script or a backup?
Go to Top of Page

mstclair
Starting Member

13 Posts

Posted - 2008-01-18 : 15:52:58
quote:
Originally posted by AndrewMurphy

The message is slighgtly garbled....

Could not find row in sysobjects for object ID 55671246 in database 'MyTable'. Run DBCC CHECKTABLE on sysobjects refers to DATABASE MyTable not a table. Is it possible a column definition is at fault....or have you (badly) edited the real error message?


No, it's not a bad edit. The message is calling the table a database. I'm very perplexed by this.

quote:
what does "select * from sysobjects" say? can you see the table in there??


Yep, the table is in there.

quote:
can the table be dropped? and re-loaded from a script or a backup?



Yes, the table can be dropped. So essentially the table is visible to sp_help and drop, but not to querying or checkdb/checktable.

Unfortunately the error was unnoticed for a long time and the client does not have backups that go back that far. They do need the data, though.
Go to Top of Page

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2008-01-18 : 16:50:52
can u bcp out the data, drop and recreate table, bulk insert back in?

ran checkdb on master?
Go to Top of Page

mstclair
Starting Member

13 Posts

Posted - 2008-01-18 : 17:21:24
bcp fails just like any other way of trying to query the table. Returns the same about table not being in sysobjects.

checkdb on master comes back 100% clean.
Go to Top of Page

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2008-01-18 : 21:14:08
u have backups right?
Go to Top of Page

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2008-01-20 : 00:25:32
Call Microsoft support if that db is important for you.
Go to Top of Page

Sara Karasik
Starting Member

10 Posts

Posted - 2008-01-25 : 13:06:37
When you run select * from sysobjects where xtype = 'u'
what is the uid of this table? If it try prefixing the table with its owner.

select * from dbo.MyTable, or select * from guest.MyTable
Go to Top of Page
   

- Advertisement -