Comparing changes names of Database objects!!?

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!

Comments

  • Hi there,

    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,
    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.

    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
    Jonathan Watts

    -Project Manager
    -Red Gate Software Ltd
  • Thank you for your help, at least now I know why its doing that.
Sign In or Register to comment.