Toad World® Forums

Another problem with Compare and Sync Data

Last beta version 600 of TOAD.

I have one table TESTATA_RAPPORTINI master and two details tables :

  1. ARTICOLI_RAPPORTINI
  2. ORE_RAPPORTINI

Obviously there is a link via foreign key between master and two details tables.
As suggest to me some days ago your colleague Stanislav, I exclude for comparison field of table that are TEXT and TIMESTAMP.

I compare and sync data and TOAD generate a script to to this.
I execute it and I discover the problem.
Toad tool want to execute an insert statement on ARTICOLI_RAPPORTINI before insert a correct record in master table TESTATA_RAPPORTINI.

This generate an error. In details :
Lookup Error - SQL Server Database Error: L’istruzione INSERT è in conflitto con il vincolo FOREIGN KEY “ARTICOLI_RAPPORTINI_fk”. Il conflitto si è verificato nella tabella “dbo.TESTATA_RAPPORTINI”, column ‘CODICE’ del database “AZServiceOld”.

This is not correct.

If someone at TOAD support post e-mail address I send to his the script generate by TOAD with the error to analyze it.

Best regards.

Sergio Bertolotti

Hi Sergio, feel free to send to me your scripts.
would be great to have DDL for three tables with primary and foreign keys plus data from these tables as a sample. Can you do it?

Valentine,

done…

See your private e-mail address for details.

I stay tune for your answer.

Best regards.

Sergio Bertolotti

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.

Please let me know it if solved your issue.

Stanislav,

I set you option DROP/CREATE FOREIGN KEYS but Obtain this error message :

Lookup Error - SQL Server Database Error: L’istruzione ALTER TABLE è in conflitto con il vincolo FOREIGN KEY “TESTATA_RAPPORTINI_fk”. Il conflitto si è verificato nella tabella “dbo.TICKETS”, column ‘CODICE’ del database “AZServiceOld”.

In attach you find a ZIP of script generated with this otion on by TOAD.
Line where the problem is presenti is 694.

Data’s are the same that I send to Velntine some minutes ago. Talk with his for details.

I stay tune for your answer.

Best regards.

Sergio Bertolotti
stanislav.zip (10.2 KB)

Sergio,

Thanks for sharing DDL and data but to reproduce this situation we also need DDL of tables that are referenced by TESTATA_RAPPORTINI table, these are: ANAGRAFICA_TECNICI, TICKETS and CAUSALI_RAPPORTINI.

Thank you.

Stanislav,

first of all, many thank’s for your support.
Second, probably I found the problem.
I’ll do some test and after inform you about the result.

Stay tune for my explanation.

Best regards.

Sergio Bertolotti

Stanislav,

I found a problem.
It’s not a TOAD problem and excuse me for the misunderstanding.
In attach a correct script of SQL to synchronize correctly these 2 DB’s.

Table TESTATA_RAPPORTINI is a master of ARTICOLI_RAPPORTINI.
dbo.TICKETS is a master of TESTATA_RAPPORTINI. There is a foreign key between these two tables.
And at the end of the story dbo.TICKETs contains a foreign key to another table ANAGRAFICA_MACCHINE.

To synchronize correctly these tables I must to drop foreign key to all these tables, pump datas between them and re-create the correct foreign key of all tables.

The new script in attach done that.
An now all is OK.

Before this, for error, I only synchronize data between TESTATA_RAPPORTINI and ARTICOLI_RAPPORTINI and not for table dbo.Tickets.
When the TOAD script re-create foreign key for TESTATA_RAPPORTINI, dbo.Tickets was not synchronize and the value in foreign key of TESTATA_RAPPORTINI was not present in dbo.Tickets.

The same identical problem was present between dbo.Tickets and ANGRAFICA_MACCHINE with the relative foregin key.

Best regards.

Sergio Bertolotti
sql_update_OK.7z (11.6 KB)