Error When Renaming Stored Procedures

marcl508marcl508 Posts: 24
edited June 20, 2006 9:54AM in SQL Toolkit Previous Versions
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.

Comments

  • Bart ReadBart Read Posts: 997 Silver 1
    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,
    Bart Read
    Principal Consultant
    bartread.com Ltd
  • Actually I simply right clicked, selected "Rename" and entered the new name. I'm assuming it uses that stored procedure under the hood.
  • Bart ReadBart Read Posts: 997 Silver 1
    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,
    Bart Read
    Principal Consultant
    bartread.com Ltd
Sign In or Register to comment.