Options
Mismatched names in stored procedure
mshiker
Posts: 4
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:
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
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
This discussion has been closed.
Comments
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,
Hope this helps.
-Project Manager
-Red Gate Software Ltd