InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comSat, 25 May 2013 07:58:28 GMT20http://www.sqlservercentral.com/Forums/Topic1000118-2807-1.aspx[quote][b]Peter Petrov (10/15/2010)[/b][hr]Nice finding! Good to know... The script could be simplified (2005 and 2008):[code="sql"]SELECT OBJECT_NAME(object_id) AS proc_name, object_id, definitionFROM sys.sql_modulesWHERE PATINDEX('%CREATE%PROCEDURE%',definition) &gt; 0AND PATINDEX('%' + OBJECT_NAME(object_id) + '%', definition) = 0ORDER BY OBJECT_NAME(object_id);[/code][/quote]Thanks Peter,Yes you are right. As mentioned in the article in 2005 and 2008 you don't have the problem of record size limitation therefore it is much easier to extract the rouge procedures.Fri, 15 Oct 2010 14:04:50 GMTMohammad Meimandihttp://www.sqlservercentral.com/Forums/Topic1000118-2807-1.aspxNice finding! Good to know... The script could be simplified (2005 and 2008):[code="sql"]SELECT OBJECT_NAME(object_id) AS proc_name, object_id, definitionFROM sys.sql_modulesWHERE PATINDEX('%CREATE%PROCEDURE%',definition) &gt; 0AND PATINDEX('%' + OBJECT_NAME(object_id) + '%', definition) = 0ORDER BY OBJECT_NAME(object_id);[/code]Fri, 15 Oct 2010 13:38:50 GMTPeter Petrovhttp://www.sqlservercentral.com/Forums/Topic1000118-2807-1.aspx[quote][b]Hardy21 (10/10/2010)[/b][hr][quote][b]Andrew Novick (10/9/2010)[/b][hr]Yes, it's a problem. I submitted an item on Connect [url=https://connect.microsoft.com/SQLServer/feedback/details/398203/sp-rename-doesnt-change-the-text-of-udfs-or-procs-sp-helptext-then-returns-a-script-with-the-old-name]https://connect.microsoft.com/SQLServer/feedback/details/398203/sp-rename-doesnt-change-the-text-of-udfs-or-procs-sp-helptext-then-returns-a-script-with-the-old-name[/url]and it was classified as "Won't Fix"It's something we're going to have to live with.Regards,AndyAndrew NovickSQL Server MVP[url=http://www.NovickSoftware.com]http://www.NovickSoftware.com[/url][/quote]Thanks author for nice article.I many times used sp_rename stored procedure but it is for our testing database. In production work, we always drop/create procedure with Grant so we did not face this issue, but good to know that if we are using 'syscomments' instead of 'sys_modules' anywhere in any script, it cause an issue.[/quote]Thanks for the comment!On production the story is totally different. Normally there are regulations and checks in place to make sure these sort of issues don't occur.Wed, 13 Oct 2010 08:09:35 GMTMohammad Meimandihttp://www.sqlservercentral.com/Forums/Topic1000118-2807-1.aspx[quote][b]Andrew Novick (10/9/2010)[/b][hr]Yes, it's a problem. I submitted an item on Connect [url=https://connect.microsoft.com/SQLServer/feedback/details/398203/sp-rename-doesnt-change-the-text-of-udfs-or-procs-sp-helptext-then-returns-a-script-with-the-old-name]https://connect.microsoft.com/SQLServer/feedback/details/398203/sp-rename-doesnt-change-the-text-of-udfs-or-procs-sp-helptext-then-returns-a-script-with-the-old-name[/url]and it was classified as "Won't Fix"It's something we're going to have to live with.Regards,AndyAndrew NovickSQL Server MVP[url=http://www.NovickSoftware.com]http://www.NovickSoftware.com[/url][/quote]Thanks!Yes it seems we have to deal with it our own wayWed, 13 Oct 2010 08:06:25 GMTMohammad Meimandihttp://www.sqlservercentral.com/Forums/Topic1000118-2807-1.aspx[quote][b]Andrew Novick (10/9/2010)[/b][hr]Yes, it's a problem. I submitted an item on Connect [url=https://connect.microsoft.com/SQLServer/feedback/details/398203/sp-rename-doesnt-change-the-text-of-udfs-or-procs-sp-helptext-then-returns-a-script-with-the-old-name]https://connect.microsoft.com/SQLServer/feedback/details/398203/sp-rename-doesnt-change-the-text-of-udfs-or-procs-sp-helptext-then-returns-a-script-with-the-old-name[/url]and it was classified as "Won't Fix"It's something we're going to have to live with.Regards,AndyAndrew NovickSQL Server MVP[url=http://www.NovickSoftware.com]http://www.NovickSoftware.com[/url][/quote]Thanks author for nice article.I many times used sp_rename stored procedure but it is for our testing database. In production work, we always drop/create procedure with Grant so we did not face this issue, but good to know that if we are using 'syscomments' instead of 'sys_modules' anywhere in any script, it cause an issue.Sun, 10 Oct 2010 22:24:16 GMTHardy21http://www.sqlservercentral.com/Forums/Topic1000118-2807-1.aspxYes, it's a problem. I submitted an item on Connect [url=https://connect.microsoft.com/SQLServer/feedback/details/398203/sp-rename-doesnt-change-the-text-of-udfs-or-procs-sp-helptext-then-returns-a-script-with-the-old-name]https://connect.microsoft.com/SQLServer/feedback/details/398203/sp-rename-doesnt-change-the-text-of-udfs-or-procs-sp-helptext-then-returns-a-script-with-the-old-name[/url]and it was classified as "Won't Fix"It's something we're going to have to live with.Regards,AndyAndrew NovickSQL Server MVP[url=http://www.NovickSoftware.com]http://www.NovickSoftware.com[/url]Sat, 09 Oct 2010 13:51:15 GMTAndrew Novickhttp://www.sqlservercentral.com/Forums/Topic1000118-2807-1.aspx[quote][b]davepaulino (10/7/2010)[/b][hr]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![/quote]Thanks!Yes it's good to always stick to the best practices.Fri, 08 Oct 2010 09:18:31 GMTMohammad Meimandihttp://www.sqlservercentral.com/Forums/Topic1000118-2807-1.aspx[quote][b]CirquedeSQLeil (10/7/2010)[/b][hr]Thanks for the article.[/quote]Thanks for reading it!Fri, 08 Oct 2010 09:17:14 GMTMohammad Meimandihttp://www.sqlservercentral.com/Forums/Topic1000118-2807-1.aspxWe also encountered this problem when we generate using sql compare application. Our workaround is to drop and recreate the procedure or functions. Great article!Thu, 07 Oct 2010 23:17:02 GMTdavepaulinohttp://www.sqlservercentral.com/Forums/Topic1000118-2807-1.aspxThanks for the article.Thu, 07 Oct 2010 18:47:19 GMTSQLRNNRhttp://www.sqlservercentral.com/Forums/Topic1000118-2807-1.aspx[quote][b]Mohammad Meimandi (10/7/2010)[/b][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.[/quote]OK, I thought that the application generated and executed the scripts, but I see I missed the part that you run the scripts manually.Thu, 07 Oct 2010 11:17:46 GMTUMG Developerhttp://www.sqlservercentral.com/Forums/Topic1000118-2807-1.aspx[quote][b]UMG Developer (10/7/2010)[/b][hr]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.)[/quote]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.Thu, 07 Oct 2010 10:24:02 GMTMohammad Meimandihttp://www.sqlservercentral.com/Forums/Topic1000118-2807-1.aspxNice 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.)Thu, 07 Oct 2010 10:07:49 GMTUMG Developerhttp://www.sqlservercentral.com/Forums/Topic1000118-2807-1.aspxFor 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]GOCreate procedure dbo.object_nameasThu, 07 Oct 2010 10:03:46 GMTSanjayAttrayhttp://www.sqlservercentral.com/Forums/Topic1000118-2807-1.aspxAgreed. DROP/CREATE is also easier to be put in a scriptThu, 07 Oct 2010 09:41:42 GMTMohammad Meimandihttp://www.sqlservercentral.com/Forums/Topic1000118-2807-1.aspxBut if you're going to rename and alter, you might as well just drop and create. It's cleaner.Thu, 07 Oct 2010 09:40:03 GMTBrandie Tarvinhttp://www.sqlservercentral.com/Forums/Topic1000118-2807-1.aspxYes after the re-name has occured if you alter procedure then it will update the syscommentsThu, 07 Oct 2010 09:24:52 GMTMohammad Meimandihttp://www.sqlservercentral.com/Forums/Topic1000118-2807-1.aspxAlter procedure will update syscomments, so will drop/creating the procedure.Thu, 07 Oct 2010 09:12:31 GMTJon McGheehttp://www.sqlservercentral.com/Forums/Topic1000118-2807-1.aspxI don't think you rename a stored procedure using 'ALTER PROCEDURE ...'Thu, 07 Oct 2010 09:11:33 GMTMohammad Meimandihttp://www.sqlservercentral.com/Forums/Topic1000118-2807-1.aspxLinked 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...Thu, 07 Oct 2010 09:06:21 GMTPeter Trasthttp://www.sqlservercentral.com/Forums/Topic1000118-2807-1.aspxCan anyone say if ALTER PROCEDURE has the same problem as sp_rename?Thu, 07 Oct 2010 09:02:48 GMTE. Ariel Smithhttp://www.sqlservercentral.com/Forums/Topic1000118-2807-1.aspxWe 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.Thu, 07 Oct 2010 08:59:29 GMTMohammad Meimandihttp://www.sqlservercentral.com/Forums/Topic1000118-2807-1.aspxI didn't get a chance to test it on 2008. Thanks for trying that as you said it is nice to knowThu, 07 Oct 2010 08:54:25 GMTMohammad Meimandihttp://www.sqlservercentral.com/Forums/Topic1000118-2807-1.aspxI 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!Thu, 07 Oct 2010 08:51:25 GMTJon McGheehttp://www.sqlservercentral.com/Forums/Topic1000118-2807-1.aspxI 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.Thu, 07 Oct 2010 07:14:51 GMTDoug Bishophttp://www.sqlservercentral.com/Forums/Topic1000118-2807-1.aspxThe 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.Thu, 07 Oct 2010 06:56:19 GMTBob Teschhttp://www.sqlservercentral.com/Forums/Topic1000118-2807-1.aspx[quote][b]Nakul Vachhrajani (10/6/2010)[/b][hr]Also, please avoid using syscomments - this will be deprecated in a future version of SQL Server. Use sys.sql_modules instead.[/quote]You can also use the really nice OBJECT_DEFINITION() function. Depending on your query it may be easier.JerryThu, 07 Oct 2010 06:55:43 GMTJerry Fosterhttp://www.sqlservercentral.com/Forums/Topic1000118-2807-1.aspx[quote][b]Nakul Vachhrajani (10/6/2010)[/b][hr] Use sys.sql_modules instead.[/quote]Thanks, Nakul. I didn't even know this table existed.Thu, 07 Oct 2010 06:08:18 GMTBrandie Tarvinhttp://www.sqlservercentral.com/Forums/Topic1000118-2807-1.aspxDropping and recreating procs is a regular part of our SDLC. We only ever use rename when we're going to archive off the proc. We want to make sure that nothing using the proc will break, so we add an _old to the end of it.But the article raises a good point that I never considered. I should verify with the developers that they aren't using DMO in any of their calls because it might invalidate our "see if it breaks" protocol.Thu, 07 Oct 2010 06:07:15 GMTBrandie Tarvinhttp://www.sqlservercentral.com/Forums/Topic1000118-2807-1.aspxInteresting ... I always noted that if you changed the name of an SP or view in EM for SQL2000 that a similar effect occurred, and you had to double click the SP/view, go behind to the source text and change the name there as well.I blamed the GUI at the time, but maybe it's this same bug.Thu, 07 Oct 2010 04:56:08 GMTben.mcintyrehttp://www.sqlservercentral.com/Forums/Topic1000118-2807-1.aspxI agree with the other comments referring to Books on line. I would add that this "feature" of SQL Server has been around for a very long time and can affect views as well. Recently I found that the issue also occurs in Sybase and we know that SQL Server as we know it now is derived from Sybase.Ian Thu, 07 Oct 2010 03:37:46 GMTIan Ragghttp://www.sqlservercentral.com/Forums/Topic1000118-2807-1.aspx[quote][b]Nakul Vachhrajani (10/7/2010)[/b][hr]For a production-grade system, you would typically have a list of permissions for each object ready. All that you would then need to do is use the GRANT clause to assign permissions to the stored procedure. This would become even easier if you are using User-schema separation wherein users would have permissions on a schema - and then the schema would in-turn have permissions on the object. Because the schema itself is not being dropped/recreated, your user permissions would not need to be reapplied.This is exactly what we do in our systems. At the end of the CREATE PROCEDURE, we would always have a GRANT clause to assign whatever permissions that come out-of-the-box with our database.[/quote]Great comment!Do we already have an article on "Good Security Practices", or is this maybe a prelude to writing one? ;-)Thu, 07 Oct 2010 01:53:18 GMTKelsey Thorntonhttp://www.sqlservercentral.com/Forums/Topic1000118-2807-1.aspx[quote][b]Petrushenya Pawel (10/7/2010)[/b][hr]Thank you for this article.One note regarding to the modifications of stored procedures through drop/create technique.When you behave in such a way you will lose security settings for targeted stored procedure.For example, 1. DBA assigned "execute" permission for certain user with name "ExampleUser".2. During application update stored procedure has been recreated using drop/create.3. User "ExampleUser" is not able to execute this stored procedure as it has been deleted earlier.This can be significant issue on Production environment and it will be difficult to explain for end users why permissions have been lost.Be aware about this issue.[/quote]For a production-grade system, you would typically have a list of permissions for each object ready. All that you would then need to do is use the GRANT clause to assign permissions to the stored procedure. This would become even easier if you are using User-schema separation wherein users would have permissions on a schema - and then the schema would in-turn have permissions on the object. Because the schema itself is not being dropped/recreated, your user permissions would not need to be reapplied.This is exactly what we do in our systems. At the end of the CREATE PROCEDURE, we would always have a GRANT clause to assign whatever permissions that come out-of-the-box with our database.Thu, 07 Oct 2010 00:46:12 GMTNakul Vachhrajanihttp://www.sqlservercentral.com/Forums/Topic1000118-2807-1.aspxThank you for this article.One note regarding to the modifications of stored procedures through drop/create technique.When you behave in such a way you will lose security settings for targeted stored procedure.For example, 1. DBA assigned "execute" permission for certain user with name "ExampleUser".2. During application update stored procedure has been recreated using drop/create.3. User "ExampleUser" is not able to execute this stored procedure as it has been deleted earlier.This can be significant issue on Production environment and it will be difficult to explain for end users why permissions have been lost.Be aware about this issue.Thu, 07 Oct 2010 00:31:17 GMTPawel PPAhttp://www.sqlservercentral.com/Forums/Topic1000118-2807-1.aspx[quote][b]Nakul Vachhrajani (10/6/2010)[/b][hr]This is bound to happen. Per BOL (http://msdn.microsoft.com/en-us/library/ms188351.aspx):[quote]Changing any part of an object name can break scripts and stored procedures. We recommend you do not use this statement to rename stored procedures, triggers, user-defined functions, or views; instead, drop the object and re-create it with the new name.[/quote]Please always drop & recreate stored procedures.[/quote]Also, please avoid using syscomments - this will be deprecated in a future version of SQL Server. Use sys.sql_modules instead.Wed, 06 Oct 2010 23:56:45 GMTNakul Vachhrajanihttp://www.sqlservercentral.com/Forums/Topic1000118-2807-1.aspxThis is bound to happen. Per BOL (http://msdn.microsoft.com/en-us/library/ms188351.aspx):[quote]Changing any part of an object name can break scripts and stored procedures. We recommend you do not use this statement to rename stored procedures, triggers, user-defined functions, or views; instead, drop the object and re-create it with the new name.[/quote]Please always drop & recreate stored procedures.Wed, 06 Oct 2010 23:55:21 GMTNakul Vachhrajanihttp://www.sqlservercentral.com/Forums/Topic1000118-2807-1.aspxComments posted to this topic are about the item [B]<A HREF="/articles/syscomments/70931/">Potential Issue with Re-Naming Stored Procedures</A>[/B]Wed, 06 Oct 2010 22:58:34 GMTMohammad Meimandi