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.
| 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 1Could not find a table or object named 'MyTable'. Check sysobjects.Msg 2501, Level 16, State 1, Line 1Could not find a table or object named 'MyTable2'. Check sysobjects.Msg 2501, Level 16, State 1, Line 1Could not find a table or object named 'MyTable3'. Check sysobjects.Msg 2501, Level 16, State 1, Line 1Could not find a table or object named 'MyTable4'. Check sysobjects.Msg 2501, Level 16, State 1, Line 1Could 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? |
 |
|
|
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. |
|
|
|
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? |
|
|
|
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. |
|
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2008-01-18 : 21:14:08
|
| u have backups right? |
|
|
|
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. |
|
|
|
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 |
|
|
|
|
|
|
|
|
Table SUPPOSEDLY missing from sysobjects