Corrupt stored procedure, can't drop, can't alter

[jacktek]

I get the below message block when I use Query Analyzer to alter stored procedure spCommon_OTCList_IDString.

Server: Msg 644, Level 21, State 3, Procedure spCommon_OTCList_IDString, Line 100
Could not find the index entry for RID '167a662d18100' in index page (1:698), index ID 0, database 'HiLine_Restored'.
ODBC: Msg 0, Level 16, State 1
Communication link failure
ODBC: Msg 0, Level 16, State 1
Communication link failure

Connection Broken

---------------------------------
If I try to drop the procedure, I get:
Server: Msg 644, Level 21, State 3, Line 1
Could not find the index entry for RID '167a662d18100' in index page (1:698), index ID 0, database 'HiLine_Restored'.

Connection Broken


-------------------------------------

If I try to create the procedure, I get:

Server: Msg 2714, Level 16, State 5, Procedure spCommon_OTCList_IDString, Line 100
There is already an object named 'spCommon_OTCList_IDString' in the database.

--------------------------------------

I have tried DBCC CHECKDB, and get the below:

Server: Msg 7995, Level 16, State 1, Line 1
Database 'HiLine_Restored' consistency errors in sysobjects, sysindexes, syscolumns, or systypes prevent further CHECKDB processing.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

----------------------------------------

If I try DBCC CHECKDB WITH PHYSICAL_ONLY, it runs, but finds no errors.

Does anyone have suggestions on how to fix this ?? The problem has existed on the customer's DB for at least 2 weeks, probably a good deal longer, so .... restoring from backup is not a good solution.

 

[zhavic]

If there is now other way, try to manualy drop stored procedure:
But before this, do the full backup of your database
And also use this on your own risk and double check you run it on the right database

/* at first we need to allow direct updates to system tables */
/* from help:
Caution  Updating fields in system tables can prevent an instance of Microsoft® SQL Server™ from running or can cause data loss. If you create stored procedures while the allow updates option is enabled, those stored procedures always have the ability to update system tables even after you disable allow updates. On production systems, you should not enable allow updates except under the direction of Microsoft Product Support Services.


Because system tables are critical to the operation of SQL Server, enable allow updates only in tightly controlled situations. Prevent other users from accessing SQL Server while you are directly updating system tables by restarting an instance of SQL Server from the command prompt with sqlservr -m. This command starts an instance of SQL Server in single-user mode and enables allow updates. For more information, see Starting SQL Server with Minimal Configuration. 
*/

EXECUTE sp_configure 'allow updates', 1
GO
RECONFIGURE WITH OVERRIDE --This setting takes effect immediately 
GO


[COLOR=red]/* DO THIS STEPS ONE BY ONE BY YOURSELF INSTEAD OF RUNNING THIS SCRIPT */[/color]

DECLARE @obj_ID integer

/* get the ID of your stored procedure */
SELECT @obj_ID = [ID]
FROM sysobjects
WHERE NAME = N'spCommon_OTCList_IDString' AND OBJECTPROPERTY( [ID], N'IsProcedure') = 1

/* if was found */
IF @@ROWCOUNT = 1
BEGIN

	/* show all objects that depends on that procedure, this objects should be recreated after you recreate your stored procedure */
	SELECT sysobjects.Name, sysobjects.[ID]
	FROM sysobjects
		INNER JOIN sysdepends ON sysdepends.[ID] = sysobjects.[ID]
	WHERE sysdepends.depid = @obj_ID
	GROUP BY sysobjects.Name, sysobjects.[ID]

	/* drop parameters for procedure */
	DELETE FROM syscolumns WHERE [ID] = @obj_ID

	/* drop contents of procedure */
	DELETE FROM syscomments WHERE [ID] = @obj_ID

	/* drop procedure */
	DELETE FROM sysobjects WHERE [ID] = @obj_ID

END

/* do not forgot disallow direct updates to system tables */
EXECUTE sp_configure 'allow updates', 0
GO
RECONFIGURE WITH OVERRIDE --This setting takes effect immediately 
GO

Zhavic

---------------------------------------------------------------
In the 1960s you needed the power of two Comodore64s to get a rocket to the moon. Now you need a machine which is a vast number of times more powerful just to run the most popular GUI.

 

[jacktek]

Zhavic -

Thanks for your reply. Evidently the DB was too hosed for your recovery script. I tried it, but got back null for the ID of the stored procedure in question. We could not manually update or delete the sysobject record for that particular object. We could change others but not that one.

I ended up migrating the customer's data to a new, empty DB. Luckily the data tables were OK. I used bcp to dump the tables to text files, and bulk insert to insert the data into the new DB.

I had planned on moving the data over if necessary - it was just that we had over 200 user tables, so I wanted to explore other options first.

Thanks again,

Jack