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

Synchronization fails after renaming table

grchristingrchristin Posts: 2
edited February 15, 2006 4:32AM in SQL Compare Previous Versions
We changed the names of a couple of tables in our DB. When trying to synchronize our development DB to our test production DB we received errors that it couldn't create some constraints because they already existed and rolled back any changes. I had to manually rename the tables, re-run the compare and then run the synchronization. It then worked. Bug? Should SQL Compare 4 be able to handle it?

Comments

  • Options
    grchristin wrote:
    We changed the names of a couple of tables in our DB. When trying to synchronize our development DB to our test production DB we received errors that it couldn't create some constraints because they already existed and rolled back any changes. I had to manually rename the tables, re-run the compare and then run the synchronization. It then worked. Bug? Should SQL Compare 4 be able to handle it?

    Hi,
    Constraint names must be unique in the database. This is an ugly limitation of SQL Server, especially since constraints can only be accessed via their parent tables. You are right that we are not handling such conflicts. On the other hand it is an interesting question about how we should handle it. Clearly we cannot just drop constraints from tables that were not selected for synchronization. Similarly we should not move constraints to other tables. One thing SQL Compare can do, is that if the renamed table is selected, it can drop it/or resolve the constraint problem by making it the same as the source table (if there is any). Therefore these conflicts require manual intervention. A similar problem occurs when one tries to migrate a table (say "herTable") to another database, but that database already has another non-table object (e.g. a function) called "herTable". Once again the only solution that can be used from SQL Compare would be to explicitly select the "herTable" function as well, in which case it will be dropped as expected, and the synchronization will succeed. But, clearly, we cannot select objects to be deleted automatically.

    Regards,
    Andras
    András Belokosztolszki, PhD
    Red Gate Software Ltd.
Sign In or Register to comment.