Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
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
Author
Message
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
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
Posted Tuesday, October 11, 2011 4:22 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 3:36 PM
Points: 12,965, Visits: 32,539
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 »

Add to briefcase

Permissions Expand / Collapse