What are the challenges you face when working across database platforms? Take the survey
Options

Object name to Definition mismatch

mikeATCompuwaremikeATCompuware Posts: 9
edited March 2, 2006 8:07AM in SQL Compare Previous Versions
There appears to be an issue when syncing objects where the underlying definition does not match the object name.
For instance, you have a view object named v_users and its definition is:
create view dbo.v_users
AS
select user_name, user_id from users

Now if you rename the view object to v_users2, the underlying view definition remains 'create view dbo.v_users AS...'

In addition to views, I think this issue applies to stored procedures and maybe other objects. Also, if the owner is changed, a similar issue will occur.

Comments

  • Options
    There appears to be an issue when syncing objects where the underlying definition does not match the object name.
    For instance, you have a view object named v_users and its definition is:
    create view dbo.v_users
    AS
    select user_name, user_id from users

    Now if you rename the view object to v_users2, the underlying view definition remains 'create view dbo.v_users AS...'

    In addition to views, I think this issue applies to stored procedures and maybe other objects. Also, if the owner is changed, a similar issue will occur.

    You are absolutely right. And unfortunately this is a sideeffect of sp_rename and of operations that change the owner of a stored procedure, view, function, etc. For this reason it is advised to avoid sp_rename (or to use it with extreme care). Basically this means that the database will be in a sort of inconsistent state (its stored procedure definition is not reflecting the statement that shoud be used to create it again). In the current version we do not support such inconsistent databases. The better way of renaming stored procedures is to drop them, and create them again.

    Regards,
    Andras
    András Belokosztolszki, PhD
    Red Gate Software Ltd.
  • Options
    I agree with your 'best practice', however I'm sure there are lots of other people like myself who get handed a code base they did not develop and which is not all consistant. Off all the issues I've been having with SQL Compare creating scripts to can actually run, this one has been the most painful for me in terms of debugging and rewriting the scripts.
Sign In or Register to comment.