I have discovered two bugs with the Compare and Sync Data Wizard in version: Toad for SQL Server 7.0.3.37 (64 bit).
The first issue is similar to that described in June, 2012, by Sergio Bertolotti:
Another problem with Compare and Sync Data
Another problem with Compare and Sync Data
Stanislav replied with the following:
"Dear Sergio,
Thanks for your post. Actually Data Compare does not track relations between tables, thus tables are presented in the synchronization script just in an alphabet order. To solve situations like you’ve just described there are special options in Synchronization Wizard. For your case you can use “Drop/create foreign keys” option, it will drop foreign key constraints at the beginning of the script and recreate at the end. This should prevent errors like one you just mentioned."
You still have to synchronize data in the parent table first. Also, it seems like once the first error was encountered, no other tables were attempted, but perhaps it was just child tables for the table in question. Since it is easy enough to get the relations data, can I ask why Quest chooses to only use alphabetic order?
I checked the option “Drop/create foreign keys” and had success, but ONLY after doing the parent table first, followed by the child tables in a second synchronization option. More importantly, when TOAD for SQL Server re-creates the foreign key constraints, it leaves them in an UNTRUSTED state! One must then run additional SQL DDL statements to trust the foreign keys. I verified that all foreign keys were trusted before running the synchronization. Afterwards, I was left with (5) untrusted foreign keys!
select
'[' + s.name + '].[' + o.name + '].[' + i.name + ']' as Untrusted_ForeignKeys,
'alter table ' + '[' + s.name + '].[' + o.name + '] with check check constraint
' + '[' + i.name + '];' as DDLToFix
from sys.foreign_keys i
join sys.objects o on i.parent_object_id = o.object_id
join sys.schemas s on o.schema_id = s.schema_id
where i.is_not_trusted = 1 and i.is_not_for_replication = 0;
go
I had to run the following (5) statements to fix this:
alter table [DataLoads].[Tasks] with check check constraint [FK_Tasks_Frequency];
alter table [DataLoads].[LoadSteps] with check check constraint [FK_LoadSteps_Task];
alter table [DataLoads].[PostSteps] with check check constraint [FK_PostSteps_Task];
alter table [DataLoads].[TaskExceptionNotifications] with check check constraint [FK_TaskExceptionNotifications_Task];
alter table [DataLoads].[TaskExceptionNotifications] with check check constraint [FK_TaskExceptionNotifications_Frequency];