How to delete record in system table 'sysobjects'

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 Development (2000)

How to delete record in system table 'sysobjects'

Author

Topic

lw1990
Yak Posting Veteran

85 Posts

Posted - 2009-02-24 : 13:25:48

Hi,
I'm running a third party program in SQL Server2000, and find that there are 2 objects by the same name, means in system table 'sysobjects' there are 2 records for the same table "LangPro.
Of course I can't just delete one like user defined tables, I login as 'sa' and use these query to delete one:
SP_CONFIGURE 'ALLOW UPDATES', 1
GO
RECONFIGURE WITH OVERRIDE
GO
delete from sysobjects where name='LangPro' and id='2144778748'
GO

Then I got error:
Configuration option 'allow updates' changed from 0 to 1. Run the RECONFIGURE statement to install.
Server: Msg 644, Level 21, State 5, Line 1
Could not find the index entry for RID '16fcb9d67f010000' in index page (1:31), index ID 3, database 'ProblemDB'.

Connection Broken

Then I ran:

SP_CONFIGURE 'ALLOW UPDATES', 0
GO
reconfigure
GO

So, what was I missing to delete a record in system table? Why I got error and didn't get the double record deleted

Thanks.

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-02-24 : 13:28:34

WHY are you doing this? Donot do like for system tables or DB might be corrupted and you will out from job? Can't you just drop table?

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-02-24 : 13:29:56

quote:
Originally posted by lw1990

Hi,
I'm running a third party program in SQL Server2000, and that found that there are 2 objects by the same name, means in system table 'sysobjects' I found 2 records for the same table "LangPro.
Of course I can't just delete one like user defined tables, I login as 'sa' and use these query to delete one:
SP_CONFIGURE 'ALLOW UPDATES', 1
GO
RECONFIGURE WITH OVERRIDE
GO
delete from sysobjects where name='LangPro' and id='2144778748'
GO

Then I got error:
Configuration option 'allow updates' changed from 0 to 1. Run the RECONFIGURE statement to install.
Server: Msg 644, Level 21, State 5, Line 1
Could not find the index entry for RID '16fcb9d67f010000' in index page (1:31), index ID 3, database 'ProblemDB'.

Looks like you have already done CORRUPTION
.

Connection Broken

Then I ran:

SP_CONFIGURE 'ALLOW UPDATES', 0
GO
reconfigure
GO

So, what was I missing to delete a record in system table? Why I got error and didn't get the double record deleted

Thanks.

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-02-24 : 13:38:28

Run this:And post in Data Corruption Topic so experts like Paul Randall can help you:

DBCC CHECKDB (DBNAME) WITH NO_INFOMSGS, ALL_ERRORMSGS

lw1990
Yak Posting Veteran

85 Posts

Posted - 2009-02-24 : 16:26:22

Thank you sodeep,
A third party program found the double records in the sysobjects table, and can not keep running.
The 2 records are:
LangPro 683253589 U 1 6 1610612736 0 0 0 2/23/2009 11:00:03 PM ....

LangPro 2144778748 U 1 6 1610612736 0 0 0 4/18/2008 11:00:12 PM ....

Even I drop the table LangPro, I still can see the second record which was created on 4/18/2008. That's why I want to delete this record from sysobjects table.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources