unusual metadata (sysobjects) 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

SQL Server 2000 Forums

SQL Server Administration (2000)

unusual metadata (sysobjects) error

Author

Topic

mstclair
Starting Member

13 Posts

Posted - 2008-01-16 : 10:12:51

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.

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.

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.

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?

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2008-01-16 : 11:23:24

is myTable owned by a user other than dbo?

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
SSMS Add-in that does a few things: www.ssmstoolspack.com <- new version out

mstclair
Starting Member

13 Posts

Posted - 2008-01-16 : 11:37:41

No, all tables are owned by dbo. I even changed them to new owner and then back to dbo, just to make sure the linking was good. I forgot to mention that I did check ownership.

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2008-01-16 : 23:52:29

What does 'dbcc checkdb' say?

mstclair
Starting Member

13 Posts

Posted - 2008-01-17 : 11:30:57

checkdb:

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.

Haywood
Posting Yak Master

221 Posts

Posted - 2008-01-17 : 18:19:18

Run DBCC CHECKTABLE(sysobjects)

Looks like you might have corruption of a system object. Have you got backups - you might post in the Corruption sub-forum and see if someone can help you out.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources