SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Right click (modify / alter / drop and create) correct vs. query on syscomments.text or sp_helptext...


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

Author
Message
ron_leg
ron_leg
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
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?
Lowell
Lowell
One Orange Chip
One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)

Group: General Forum Members
Points: 28054 Visits: 39926
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

--
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!

Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search