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 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy