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

Can't compare/synch even with an empty db

EricEric Posts: 35
edited September 15, 2006 6:31AM in SQL Compare Previous Versions
There seem to be a problem with version 5.1 and 5.2/SQL 2005.
The objects are created in the wrong order, so creation fails. Even doing a compare between a tiny db and an empty one results in a failed synchronization process. The type of error I get is:
The following error message was returned from the SQL Server:
[1776] There are no primary or candidate keys in the referenced table 'dbo.cSaleAssign' that match the referencing column list in the foreign key 'FK_cSaleSplit_cSaleAssign'.
Could not create constraint. See previous errors.

The following SQL command caused the error:
ALTER TABLE [dbo].[cSaleSplit] ADD
CONSTRAINT [FK_cSaleSplit_cSaleAssign] FOREIGN KEY ([SaleAssignTK]) REFERENCES [dbo].[cSaleAssign] ([SaleAssignTK])

Is this a known problem?
If yes, is there a fix on the way?
If no, do you need a way to reproduce it?

Thanks

Eric

Comments

  • Options
    Hi Eric,

    If you're synchronizing 2000 to 2005, try swapping the order of the servers in the project settings (2000 on the right and 2005 on the left). The program may be generating SQL 2005 syntax that you're trying to run on a 2000 server.
  • Options
    Hi Brian,

    No, I was synchronizing from a 2005 db to another 2005 db. I then tried between the same first db and a totally empty one (on the same 2005 server).

    By totally empty, I mean it: no tables, views, sp, fn, etc. I just ran "Create database Test" in SSMS and then tried a Compare between that empty db and another one that had some user objects (about 20 tables, a couple of views and a CLR assembly)
  • Options
    Hi Eric,

    Can you create some SQL scripts that reproduce the problem?
  • Options
    Yes, I can supply scripts. Is there a way to send them to you without posting them publicly on the forum?
  • Options
    Please send them to support at red-gate.com.
  • Options
    Done.

    Thanks
  • Options
    Hi Eric,

    The error 1776 indicates that you need a primary key constraint on the cSaleAssign table’s SaleAssignTK column before the foreign key can be created. I didn’t see a creation command that would put a primary key on the necessary column anywhere in the script that you’d sent. My thinking is that the database had the primary key on at some point, then it may have been removed.

    The solution should be to reinstate the foreign key on the table that I’d mentioned in the last paragraph. If that’s not the problem, please let me know.
  • Options
    Brian,

    If you look in the source script, you will find the following line:

    ALTER TABLE [dbo].[cSaleAssign] ADD CONSTRAINT [PK_cSaleAssign] PRIMARY KEY NONCLUSTERED ([SaleAssignTK])


    So the table DOES have a PK. Anyway, the update script is generated by SQL Compare by comparing the source DB with an empty database; so it can't see a FK on a non existent PK.
  • Options
    Hi,

    The problem, in the end, was that the SQL Compare project was set to ignore indexes. This caused SQL Compare not to script the primary key, leading to a failure when trying to create the foreign key.
This discussion has been closed.