Comparing changes names of Database objects!!?
rmontgomery@rpionline.com
Posts: 3
When I run SQL Compare it sometimes changes the name of my views, stored procedures, etc. Why does it do this and can I turn it off? Is it a bug?
Example:
Name of view in database: v_ItemSalesNewPD_YTD
SQL Compares script:
---
CREATE VIEW dbo.v_NewItemSalesPD_YTD
AS
SELECT TOP........
---
OR
Name of Stored Procedure: usp_course_session_statuses_get
SQl Compare script:
---
CREATE PROCEDURE [dbo].i]usp_course_session_status_get[/i AS
SELECT status_id..........
---
It just makes up a new name out of nowhere. While this may appear to be helpful, it is really messing things up.
Very frustrating. Please Help!
Example:
Name of view in database: v_ItemSalesNewPD_YTD
SQL Compares script:
---
CREATE VIEW dbo.v_NewItemSalesPD_YTD
AS
SELECT TOP........
---
OR
Name of Stored Procedure: usp_course_session_statuses_get
SQl Compare script:
---
CREATE PROCEDURE [dbo].i]usp_course_session_status_get[/i AS
SELECT status_id..........
---
It just makes up a new name out of nowhere. While this may appear to be helpful, it is really messing things up.
Very frustrating. Please Help!
Comments
The most likely reason for the behaviour you are seeing is that objects have been renamed using Enterprise Manager, Management Studio or the sp_rename system stored procedure.
Unfortunately, there is an issue with the sp_rename procedure, which is used by EM and SSMS, as it does not rename an object's definition in the syscomments(SQL2000) or sys.sql_modules(SQL2005) system 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/archive/2006/05/10/783.aspx
Admittedly SQL Compare could handle this situation better than it currently does, and as this is a very common request from customers we may look into a dealing with sp_renamed objects in the next version of SQL Compare.
If you have to rename an object, 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,
Alternatively, Red Gate's SQL Refactor can create you a rename script that will correctly rename your object and any dependencies that use this object.
Hope this helps.
Regards,
Jonathan
-Project Manager
-Red Gate Software Ltd