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 ««1234»»»

Potential Issue with Re-Naming Stored Procedures Expand / Collapse
Author
Message
Posted Thursday, October 7, 2010 6:55 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, October 26, 2012 2:29 PM
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
Post #1000399
Posted Thursday, October 7, 2010 6:56 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, October 7, 2010 10:43 AM
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.
Post #1000400
Posted Thursday, October 7, 2010 7:14 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, April 5, 2011 12:56 PM
Points: 13, 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.
Post #1000441
Posted Thursday, October 7, 2010 8:51 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, February 20, 2014 12:27 PM
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!
Post #1000574
Posted Thursday, October 7, 2010 8:54 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, June 14, 2013 7:41 AM
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
Post #1000582
Posted Thursday, October 7, 2010 8:59 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, June 14, 2013 7:41 AM
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
Post #1000590
Posted Thursday, October 7, 2010 9:02 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, December 8, 2014 9:34 AM
Points: 1, Visits: 30
Can anyone say if ALTER PROCEDURE has the same problem as sp_rename?
Post #1000595
Posted Thursday, October 7, 2010 9:06 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Friday, June 13, 2014 3:03 PM
Points: 594, 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
Post #1000602
Posted Thursday, October 7, 2010 9:11 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, June 14, 2013 7:41 AM
Points: 30, Visits: 81
I don't think you rename a stored procedure using 'ALTER PROCEDURE ...'

Moe M
Database Consultant
http://www.cubeangle.com
Post #1000607
Posted Thursday, October 7, 2010 9:12 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, February 20, 2014 12:27 PM
Points: 83, Visits: 597
Alter procedure will update syscomments, so will drop/creating the procedure.
Post #1000608
« Prev Topic | Next Topic »

Add to briefcase ««1234»»»

Permissions Expand / Collapse