Click here to monitor SSC
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in
Home       Members    Calendar    Who's On

Add to briefcase

Right click (modify / alter / drop and create) correct vs. query on syscomments.text or sp_helptext return in-correct object definition Expand / Collapse
Posted Tuesday, October 11, 2011 12:51 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, October 11, 2011 12:41 AM
Points: 1, Visits: 0
In a SQL 2000, I have 2 stored procedures
and using sp_helptext to see the SQL definition of that object

exec sp_helptext sproc1
create procedure sproc1

exec sp_helptext sproc2
create procedure sproc1 -- That is not a typo ....sproc1

I also tried a query like:
syscomments inner joined with sysobjects to return:
There are a few rows of syscomments.text
create procedure sproc1
for both stored procedure object.

yet when I use SSMS & right-click modify / alter / drop and create
... procedure sproc1 or sproc2 -- depending where the right click was done

BTW-the same result happened in SQL 2008 (not R2) SQL Server.

Let me please ask the questions:
How could the right click (modify / alter / drop and create) be correct yet a query on the system tables or system stored procedures return in-correct?
Does this mean that the system tables of the user database are corrupt?
Is there a way to fix this? DBCC CheckDB?

Thank you for your time and any ideas to fix this system table corruption?
Post #1188226
Posted Tuesday, October 11, 2011 4:22 AM



Group: General Forum Members
Last Login: Today @ 11:52 AM
Points: 14,538, Visits: 38,376
i remember that SQL 2000, via enterprise manager, it is possible to use the GUI to change the text of the procedure, but without executing that new definition , so you could have a definition that is different than the compiled body of the proc.

so you are seeing effectively the same issue in 2008? i thought that was addressed by SSMS always scripting it out? or are you looking at a 2000 server that that effect may have occurred?


help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!
Post #1188316
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse