Can't compare/synch even with an empty db

Eric

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

Brian Donahue

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.

Eric

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)

Brian Donahue

Hi Eric,

Can you create some SQL scripts that reproduce the problem?

Eric

Yes, I can supply scripts. Is there a way to send them to you without posting them publicly on the forum?

Brian Donahue

Please send them to support at red-gate.com.

Eric

Done.

Thanks

Brian Donahue

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.

Eric

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.

Brian Donahue

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.