Potential Issue with Re-Naming Stored Procedures

Question

Potential Issue with Re-Naming Stored Procedures

Viewing 15 posts - 16 through 30 (of 36 total)

You must be logged in to reply to this topic. Login to reply

Moe M Old Hand Points: 364 More actions · Answer 1

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 Newbie Points: 9 More actions · Answer 2

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

Peter Trast SSCarpal Tunnel Points: 4392 More actions · Answer 3

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 Old Hand Points: 364 More actions · Answer 4

I don't think you rename a stored procedure using 'ALTER PROCEDURE ...'

Moe M
Database Consultant
http://www.cubeangle.com

Jon McGhee SSC-Addicted Points: 487 More actions · Answer 5

Alter procedure will update syscomments, so will drop/creating the procedure.

Moe M Old Hand Points: 364 More actions · Answer 6

Yes after the re-name has occured if you alter procedure then it will update the syscomments

Moe M
Database Consultant
http://www.cubeangle.com

Brandie Tarvin SSC Guru Points: 172994 More actions · Answer 7

But if you're going to rename and alter, you might as well just drop and create. It's cleaner.

Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

Moe M Old Hand Points: 364 More actions · Answer 8

Agreed. DROP/CREATE is also easier to be put in a script

Moe M
Database Consultant
http://www.cubeangle.com

SanjayAttray SSChampion Points: 13157 More actions · Answer 9

For the same reason I never use sp_rename to rename object in sql server.

Always prefer if exist method.

if exists (select * from sys.objects where object_id = object_id(N'[dbo].[Object_name]') and OBJECTPROPERTY(object_id, N'IsProcedure') = 1)

drop procedure [dbo].[object_name]

GO

Create procedure dbo.object_name

as

SQL DBA.

UMG Developer SSChampion Points: 13482 More actions · Answer 10

Nice article, I think it is good to bring light to this situation.

However, I think it is interesting that you only found the problem because of bad data on a report. Does your application to copy the database not report the errors it encounters while transferring objects? (Proper error handling could have saved you hours of investigation.)

Moe M Old Hand Points: 364 More actions · Answer 11

UMG Developer (10/7/2010)
Nice article, I think it is good to bring light to this situation.
However, I think it is interesting that you only found the problem because of bad data on a report. Does your application to copy the database not report the errors it encounters while transferring objects? (Proper error handling could have saved you hours of investigation.)

Well as far as the application is concerned no error has occured here. The SQLDMO pulled all objects and scripted them successfully. Only when running the generated script have you the chance to check the errors and see if any of your create statements have failed due to duplicate create statements and of course we failed at this point.

Moe M
Database Consultant
http://www.cubeangle.com

UMG Developer SSChampion Points: 13482 More actions · Answer 12

Mohammad Meimandi (10/7/2010)[hrWell as far as the application is concerned no error has occured here. The SQLDMO pulled all objects and scripted them successfully. Only when running the generated script have you the chance to check the errors and see if any of your create statements have failed due to duplicate create statements and of course we failed at this point.

OK, I thought that the application generated and executed the scripts, but I see I missed the part that you run the scripts manually.

SQLRNNR SSC Guru Points: 281334 More actions · Answer 13

Thanks for the article.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

davepaulino Newbie Points: 5 More actions · Answer 14

We also encountered this problem when we generate using sql compare application. Our workaround is to drop and recreate the procedure or functions. Great article!

Moe M Old Hand Points: 364 More actions · Answer 15

CirquedeSQLeil (10/7/2010)
Thanks for the article.

Thanks for reading it!

Moe M
Database Consultant
http://www.cubeangle.com