"Syscomments" system table corrupted

[www Developerof NET]

Hi, I have SQL 2000 installed on my development machine. Things were fine till yesterday, but then things started turning out nasty. I created an SP with name sp_retrieve_ageinginward_details ( I am aware that naming SPs with "sp_" as a prefix is not good ). Today just to make a minor change i scripted the SP, but it returns an error:

---------------------------
SQL Query Analyzer

[SQL-DMO]Object 'dbo.sp_retrieve_ageinginward_details' was not scripted. It is possible that the information in system table 'syscomments' about the object was corrupt or missing.

OK

Also when i check the script from syscomments it gives me an error in an C++ file.

Server: Msg 3624, Level 20, State 1, Line 1

Location: recbase.cpp:1371
Expression: m_nVars > 0
SPID: 57
Process ID: 556

Connection Broken

How can i retrieve my SP from my DB :sigh: :sigh: , since its huge and i have no backup. :(( :(( Pls help.......

When you fail to plan, you are planning to fail.

[Ashfield]

If you can see it in object explorer try right click and that will give you an option to script it. Other than that, you may be able to pull the info out of syscomments by direct sql (id joins to id from sysobjects) If this fails, then you have learnt the lesson - always save your sql to a flat file and put in in a vserion control system.

Bob Ashfield Consultants Ltd Proud to be a 2009 Code Project MVP