October 11, 2011 at 12:51 am
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?
October 11, 2011 at 4:22 am
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
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply