Potential Issue with Re-Naming Stored Procedures

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss content posted by Mohammad Meimandi » Potential Issue with Re-Naming Stored...

37 posts, Page 2 of 4

««1 2 3 4 »»»

Potential Issue with Re-Naming Stored Procedures

Rate Topic

Display Mode

Topic Options

Author

Message

Jerry Foster

Posted Thursday, October 07, 2010 6:55 AM

Forum 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

Bob Tesch

Posted Thursday, October 07, 2010 6:56 AM

Forum Newbie

Group: General Forum Members
Last Login: Thursday, October 07, 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

Doug Bishop

Posted Thursday, October 07, 2010 7:14 AM

Grasshopper

Group: General Forum Members
Last Login: Tuesday, April 05, 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

Jon McGhee

Posted Thursday, October 07, 2010 8:51 AM

SSC Journeyman

Group: General Forum Members
Last Login: Friday, December 21, 2012 8:00 AM
Points: 83, Visits: 592

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

Mohammad Meimandi

Posted Thursday, October 07, 2010 8:54 AM

SSC Rookie

Group: General Forum Members
Last Login: Today @ 2:46 PM
Points: 29, Visits: 64

I didn't get a chance to test it on 2008. Thanks for trying that as you said it is nice to know

Mohammad Meimandi
Database Consultant
http://www.cubeangle.com

Post #1000582

Mohammad Meimandi

Posted Thursday, October 07, 2010 8:59 AM

SSC Rookie

Group: General Forum Members
Last Login: Today @ 2:46 PM
Points: 29, Visits: 64

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.

Mohammad Meimandi
Database Consultant
http://www.cubeangle.com

Post #1000590

E. Ariel Smith

Posted Thursday, October 07, 2010 9:02 AM

Forum Newbie

Group: General Forum Members
Last Login: Friday, August 31, 2012 9:13 AM
Points: 1, Visits: 24

Can anyone say if ALTER PROCEDURE has the same problem as sp_rename?

Post #1000595

Peter Trast

Posted Thursday, October 07, 2010 9:06 AM

Mr or Mrs. 500

Group: General Forum Members
Last Login: Friday, March 15, 2013 10:35 AM
Points: 594, Visits: 654

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