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 9:24 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
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
Post #1000625
Posted Thursday, October 7, 2010 9:40 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Monday, August 25, 2014 7:14 AM
Points: 7,197, Visits: 6,341
But if you're going to rename and alter, you might as well just drop and create. It's cleaner.

Brandie Tarvin, MCITP Database Administrator

Webpage: http://www.BrandieTarvin.net
LiveJournal Blog: http://brandietarvin.livejournal.com/
On LinkedIn!, Google+, and Twitter.

Freelance Writer: Shadowrun
Latchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.
Post #1000637
Posted Thursday, October 7, 2010 9:41 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
Agreed. DROP/CREATE is also easier to be put in a script

Moe M
Database Consultant
http://www.cubeangle.com
Post #1000640
Posted Thursday, October 7, 2010 10:03 AM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Thursday, July 17, 2014 10:56 AM
Points: 3,924, Visits: 1,607
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.
Post #1000664
Posted Thursday, October 7, 2010 10:07 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, June 16, 2014 9:38 AM
Points: 2,163, Visits: 2,189
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.)
Post #1000665
Posted Thursday, October 7, 2010 10:24 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
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
Post #1000682
Posted Thursday, October 7, 2010 11:17 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, June 16, 2014 9:38 AM
Points: 2,163, Visits: 2,189
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.
Post #1000729
Posted Thursday, October 7, 2010 6:47 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 8:01 PM
Points: 21,642, Visits: 15,312
Thanks for the article.



Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp
Post #1000978
Posted Thursday, October 7, 2010 11:17 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, October 7, 2010 11:14 PM
Points: 1, Visits: 0
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!
Post #1001021
Posted Friday, October 8, 2010 9:17 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
CirquedeSQLeil (10/7/2010)
Thanks for the article.


Thanks for reading it!


Moe M
Database Consultant
http://www.cubeangle.com
Post #1001395
« Prev Topic | Next Topic »

Add to briefcase «««1234»»

Permissions Expand / Collapse