Error When Renaming Stored Procedures

marcl508

I renamed a stored procedure from sp_SetDocumentPartSort to g4_Document_Sort_Parts. This was the only change to the database.

When I ran a compairison I got the following:

PRINT N'Creating [dbo].b]g4_Document_Sort_Parts[/b'
GO
CREATE PROCEDURE dbo.sp_SetDocumentPartSort
(

I made some cosmetic changes to g4_Document_Sort_Parts (added a comment) and it started working.

Looks like the is a bug when the only change is a change to the name of the object.

Bart Read

Hi there,


Thanks for your post. I'm assuming you used sp_rename to rename the stored procedure? Unfortunately, for reasons explained in the following blog article this turns out not to be a very good idea:

http://blogs.red-gate.com/blogs/andras/ ... 0/783.aspx

Admittedly SQL Compare could handle this slightly better than it currently does, however the behaviour is essentially an artifact of the slightly bizarre behaviour exhibited by SQL Server when sp_rename is used. Under these circumstances the best practice I'd recommend is to drop and recreate the stored procedure. Oddly enough, having checked SQL Server 2000 Books Online, I even found the same recommendation in there:

"Stored procedures and views can be dropped and re-created quickly because neither object stores data. For best results renaming textual objects, drop and re-create the object by its new name."

So it looks like you should do a similar thing for views as well.

Hope that helps you out.


Many thanks,

marcl508

Actually I simply right clicked, selected "Rename" and entered the new name. I'm assuming it uses that stored procedure under the hood.

Bart Read

Hi marcl508,


Thanks for getting back to me: you're quite correct about that right-click menu option. It's basically evil so I'd strongly suggest you avoid using it. The best thing is simply to script out your stored procedure, drop it, then edit the script to change the name, and run the script. This also goes for other text objects such as views and functions.


Cheers,