Mismatched names in stored procedure

mshiker

I'm not sure if this is intended behavior in Microsoft's SQL Server Management Studio (SSMS) (2005) or if it is a bug in SQL Compare but here is a situtation we came across this moring that I would like to find out if reg-gate is aware of it.

In SSMS you can now rename a stored procedure through the user interface and the internal ALTER statement for that stored procedure will be synchronized. For example, I can select a stored procedure from the list of stored procedures, right click, and rename a stored procedure called MyOriginalSP to MyNewSP. The name of the stored procedure in the list and the ALTER statement will both be MyNewSP.

When I run SQL Compare against our Dev database which has the stored procedure now called MyNewSP and our Production database which has the stored procedure called MyOriginalSP, the resulting script will have this:

PRINT N'Creating [dbo].[MyNewSP]'

CREATE PROCEDURE [dbo].[MyOriginalSP]
...

SQL Compare uses the new name in the PRINT statement but uses the original name in the CREATE statement.

If I go back into our Dev database using SSMS and choose to modify MyNewSP and then click the Execute button to execute the ALTER script and then rerun SQLCompare the change is picked up properly. That is, MyOriginalSP is dropped and MyNewSP is created.

That is where I'm not sure if that is a Microsoft issue or reg-gate issue. Maybe Microsoft is properly modifying the ALTER statement but is not properly executing the ALTER statement after the renaming. Maybe red-gate is reading from an incorrect system table. If it is a reg-gate issue it might be a bug. If it is Microsoft, then I guess we'll have to do the work around for renaming the stored procedures then executing the ALTER statement.

Thanks!

Marc

JonathanWatts

Hi there,

Using the rename option in Enterprise Manager or Management Studio calls the sp_rename system stored procedure under the hood. Unfortunately, sp_rename does not rename the object definition in the syscomments(SQL2000) or sys.sql_modules(SQL2005) tables so causes this inconsistancy that you are seeing in SQL Compare.

For further details on sp_rename see this blog article: 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, in fact the sp_rename entry in SQL 2005 Books Online actually states this,

SQL Server Books Online sp_rename wrote:

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.

Hope this helps.