Stored Procedures and Functions created with wrong name

Category: Troubleshooting & error messages
Date: 26 Nov 2008
Product: SQL Compare
Versions: 3,4,5
Fix Version: 6.0

When using SQL Compare, the side-by- side windows and the SQL synchronization script does not accurately reflect the object name as it appears in Enterprise Manager or Management Studio.

The cause is that sp_rename has been used against the object or the object had been renamed in Management Studio or Enterprise Manager. When SQL objects are renamed in this way, the syscomments system table is not updated. SQL Compare relies on the information in syscomments to construct the migration SQL..

To reproduce, rename the SalesByCategory stored procedure in Microsoft's excample Northwind database and run the following script:

select c.text from syscomments c inner join sysobjects o on o.id=c.id where o.name='SalesByCategory'

You will see that the text still reflects the old object name.

The workaround is to change your habits not to use sp_rename to change object names, but DROP and CREATE the object instead. This will feed the new object name into syscomments.

Note that SQL Compare version 6 contains a workaround, so upgrading will also resolve the issue.

Document ID: KB200705000019 Keywords: SQL, Compare, side-by-side, reanme, stored procedure, obj

Was this article helpful?

Search support