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 Friday, October 8, 2010 9:18 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
davepaulino (10/7/2010)
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!


Thanks!

Yes it's good to always stick to the best practices.


Moe M
Database Consultant
http://www.cubeangle.com
Post #1001396
Posted Saturday, October 9, 2010 1:51 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, October 31, 2014 12:30 PM
Points: 4, Visits: 113
Yes, it's a problem. I submitted an item on Connect
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
and it was classified as "Won't Fix"

It's something we're going to have to live with.

Regards,
Andy
Andrew Novick
SQL Server MVP
http://www.NovickSoftware.com
Post #1001751
Posted Sunday, October 10, 2010 10:24 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, November 20, 2014 5:13 AM
Points: 1,131, Visits: 1,394
Andrew Novick (10/9/2010)
Yes, it's a problem. I submitted an item on Connect
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
and it was classified as "Won't Fix"

It's something we're going to have to live with.

Regards,
Andy
Andrew Novick
SQL Server MVP
http://www.NovickSoftware.com


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.


Thanks
Post #1001907
Posted Wednesday, October 13, 2010 8:06 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
Andrew Novick (10/9/2010)
Yes, it's a problem. I submitted an item on Connect
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
and it was classified as "Won't Fix"

It's something we're going to have to live with.

Regards,
Andy
Andrew Novick
SQL Server MVP
http://www.NovickSoftware.com


Thanks!

Yes it seems we have to deal with it our own way


Moe M
Database Consultant
http://www.cubeangle.com
Post #1003645
Posted Wednesday, October 13, 2010 8:09 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
Hardy21 (10/10/2010)
Andrew Novick (10/9/2010)
Yes, it's a problem. I submitted an item on Connect
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
and it was classified as "Won't Fix"

It's something we're going to have to live with.

Regards,
Andy
Andrew Novick
SQL Server MVP
http://www.NovickSoftware.com


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.


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.


Moe M
Database Consultant
http://www.cubeangle.com
Post #1003651
Posted Friday, October 15, 2010 1:38 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: 2 days ago @ 3:50 PM
Points: 1,199, Visits: 362
Nice finding! Good to know...

The script could be simplified (2005 and 2008):

SELECT	OBJECT_NAME(object_id) AS proc_name
, object_id
, definition
FROM sys.sql_modules
WHERE PATINDEX('%CREATE%PROCEDURE%',definition) > 0
AND PATINDEX('%' + OBJECT_NAME(object_id) + '%', definition) = 0
ORDER BY OBJECT_NAME(object_id);




Post #1005538
Posted Friday, October 15, 2010 2:04 PM
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
Peter Petrov (10/15/2010)
Nice finding! Good to know...

The script could be simplified (2005 and 2008):

SELECT	OBJECT_NAME(object_id) AS proc_name
, object_id
, definition
FROM sys.sql_modules
WHERE PATINDEX('%CREATE%PROCEDURE%',definition) > 0
AND PATINDEX('%' + OBJECT_NAME(object_id) + '%', definition) = 0
ORDER BY OBJECT_NAME(object_id);



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.


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

Add to briefcase «««1234

Permissions Expand / Collapse