Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Potential Issue with Re-Naming Stored Procedures


Potential Issue with Re-Naming Stored Procedures

Author
Message
Jerry Foster
Jerry Foster
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: 36
Nakul Vachhrajani (10/6/2010)

Also, please avoid using syscomments - this will be deprecated in a future version of SQL Server. Use sys.sql_modules instead.


You can also use the really nice OBJECT_DEFINITION() function. Depending on your query it may be easier.

Jerry
Bob Tesch
Bob Tesch
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: 5
The only reason I ever give a plug for a vendor is because I have used the product and it has saved my bacon. Red Gate has a utility that does a "Smart Rename". IF YOU EVER HAVE TO DO RENAMING OF ANYTHING IN THE DATABASE, this utility pays for itself. Good, clean, and complete. It does the job. We actually bought the entire SQL ToolBelt and while it looks expensive, I have paid for it in saved time (and prevented a migration rollback) many times over.
Doug Bishop
Doug Bishop
SSC Rookie
SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)

Group: General Forum Members
Points: 25 Visits: 67
I also have used smart rename in SQL Refractor. However, I am a consultant and it is my own property, so not everyone on our team has it.
It is very common pratice to rename a sotred proc with an _OLD suffix when creating a new version of it. I am in the habit, and have communicated to others, that when renaming procs, they MUST open it in EM, change the name in the CREATE PROC line to match the renamed proc name and save it.
One of the pitfalls of not doing that is if you ever, as the article pointed out, script the database and need to run the script. SQL Server generates DROP statements for all objects at the beginnng of the script. Subsequently, all objects are then created. You will get an error that the object already exists and then the fun begins.
I would definately label this as a SQL Server bug and I am very disappointed to see it is still present in SQL Server 2005, especially when using sp_rename. It should take care of renaming the object in syscomments.
Jon McGhee
Jon McGhee
SSC Journeyman
SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)

Group: General Forum Members
Points: 83 Visits: 597
I tested this with SMO scripting (2008), and it worked ok, the renamed procedure scripted with the correct name. After scripting I checked syscomments, and it will still showing the wrong procedure name. Nice to know!
Moe M
Moe M
SSC Rookie
SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)

Group: General Forum Members
Points: 30 Visits: 81
I didn't get a chance to test it on 2008. Thanks for trying that as you said it is nice to know

Moe M
Database Consultant
http://www.cubeangle.com
Moe M
Moe M
SSC Rookie
SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)

Group: General Forum Members
Points: 30 Visits: 81
We achive the same using an 'execute_all' role that has permission execute on all SPs. We just need to update the role in that case.

Moe M
Database Consultant
http://www.cubeangle.com
E. Ariel Smith
E. Ariel Smith
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: 32
Can anyone say if ALTER PROCEDURE has the same problem as sp_rename?
Peter Trast
Peter Trast
SSChasing Mays
SSChasing Mays (652 reputation)SSChasing Mays (652 reputation)SSChasing Mays (652 reputation)SSChasing Mays (652 reputation)SSChasing Mays (652 reputation)SSChasing Mays (652 reputation)SSChasing Mays (652 reputation)SSChasing Mays (652 reputation)

Group: General Forum Members
Points: 652 Visits: 655
Linked on my LinkedIn profile. I wonder how wide the scope of this issue is. I have seen rename used many times and I bet there are alot of DBA's that will be glad to find out about this...

Peter Trast
Microsoft Certified ...(insert many literal strings here)
Microsoft Design Architect with Alexander Open Systems
Moe M
Moe M
SSC Rookie
SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)

Group: General Forum Members
Points: 30 Visits: 81
I don't think you rename a stored procedure using 'ALTER PROCEDURE ...'

Moe M
Database Consultant
http://www.cubeangle.com
Jon McGhee
Jon McGhee
SSC Journeyman
SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)

Group: General Forum Members
Points: 83 Visits: 597
Alter procedure will update syscomments, so will drop/creating the procedure.
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