Right click (modify / alter / drop and create) correct vs. query on syscomments.text or sp_helptext return in-correct object definition

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 7,2000 » Data Corruption » Right click (modify / alter / drop and...

Right click (modify / alter / drop and create) correct vs. query on syscomments.text or sp_helptext return in-correct object definition

Rate Topic

Display Mode

Topic Options

Author

Message

ron_leg

Posted Tuesday, October 11, 2011 12:51 AM

Forum 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
sproc1
sproc2
and using sp_helptext to see the SQL definition of that object

exec sp_helptext sproc1
returns:
...
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:
syscomments.text
sysobjects.name
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

Lowell

Posted Tuesday, October 11, 2011 4:22 AM

SSChampion

Group: General Forum Members
Last Login: Today @ 7:41 AM
Points: 11,617, Visits: 27,676

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?

Lowell

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son

Post #1188316

« Prev Topic | Next Topic »

Permissions