Foreign Key differences not included in SQL Scrip

chev265

Hi, I am currently evaluating SQL Compare 6.2 and am having trouble with foreign keys.

I have a table called Budget that is listed in the 'objects that exist in both but are different' category. The only difference that is picked up is that the source database has an extra foreign key. This is as I expected. The problem is if I then run the Snych wizard (with this table included/checked) or look at the SQL for that particular object it only scripts out the following without any actual object changes (foreign keys and check constraints are not checked as being ignored in the project settings). The only other thing that may be a bit out of the ordinary is that all tables, included this one, are in their own schema not dbo under SQL 2005.

SET NUMERIC_ROUNDABORT OFF
GO
SET ANSI_PADDING, ANSI_WARNINGS, CONCAT_NULL_YIELDS_NULL, ARITHABORT, QUOTED_IDENTIFIER, ANSI_NULLS ON
GO
SET XACT_ABORT ON
GO
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
GO

Michelle T

Could you send a database snapshot or backup to michelle.taylor@red-gate.com so I can have a look and try and work out what the problem is? The tables being in a different schema shouldn't be causing this problem - it sounds like we're not picking up the foreign key correctly. Hopefully if I get a look at the table in question (and everything linked to it) I can find the problem and get it fixed.

chev265

Hi,

Thanks for your reply. I have just been preparing the databases to send through to you and actually discovered the source of the problem. In the database where the foreign key exists (source) the foreign table is a table, but the destination database that table is actually a view hence the foreign key cannot actually be created! oops.

Is there a way to filter out those differences? I don't want to filter out all foreign constraint differences, only those that cannot be created.

Sorry for the trouble.
Thanks
James

Michelle T

When I run the synch wizard on a database with a table set up with such a foreign key, the wizard tries to include the table which the foreign key depends on, and only ignores the foreign key if I uncheck 'include dependancies'. What version of SQL Compare are you using?

chev265

I am using V6.2, and you are right that's what it does if you select to include dependencies (which I wasn't).

But, the thing is the script will fail anyway because there is already an object in the destination database with that name (the view). And the foreign keys cannot be created against a view as well so you end up with mutliple errors. I want it to completely ignore those foreign keys that cannot be scripted.

Michelle T

It's likely that if we fixed this (I spotted the problem with the view and added that to our bug list, so it may well get fixed for the next release) we'd fix it so that the table would synchronize over the view and the foreign key would be synchronized properly, rather than that the table would be reported as the same because it couldn't be synchronized without its dependancies.

To get the feature request right: is what you're asking for an option which would only display as differences the kind of differences that can be synchronized without synchronizing any dependant objects? (i.e. only showing differences that can be synchronized with Include Dependancies off)?

chev265

Great that you may well be fixing that in the next release, but if it is resolved in the manner you describe then that wouldn't actually help me.

With regards to the feature request, you have it right. If that feature was implemented then that would do the trick and would in fact help me out a lot.

Thanks for all your help.