Updating databases - foreign keys problem

TheBatA

Hi!
I have the following problem with SQL toolkit 6:

I have one database that needs to be updated. Almost all tables are related and have foreign keys.

I need to update some of those tables, not all of them. In another database I have tables with updated data (full tables, not just different rows).
In one datagrid I have list of tables and chechboxes that are used to select tables to update.
If I select only those tables I want to update, I get correct data for update, but when I create a package using SQL Packager, I get an error
"The DELETE statement conflicted with the REFERENCE constraint " and table on which error occured isn't in list for data compare (at least I didn't selected it).

I use following code for selecting data for compare:

            db1.RegisterForDataCompare(new ConnectionProperties(cbSrvSrc.Text, cbDbSrc.Text));
            db2.RegisterForDataCompare(new ConnectionProperties(cbSrvDest.Text, cbDbDest.Text));
            TableMappings mappings = new TableMappings();
            foreach (DataGridViewRow dgr in dgvTabele.Rows)
            {
                if ((bool)dgr.Cells[1].Value == true)
                {
                    TableMapping tableMapping = (TableMapping)mappings.Join(db1.Tables[dgr.Cells[0].Value.ToString()], db2.Tables[dgr.Cells[0].Value.ToString()]);
                    tableMapping.Where = new WhereClause(dgr.Cells[2].Value.ToString());
                    mappings.Add(tableMapping);

                }

            }
            session = new ComparisonSession();
            session.CompareDatabases(db1, db2, mappings);

...

            schemaMappings = new SchemaMappings();
            schemaMappings.CreateMappings(db1, db2);
‚            SqlProvider provider = new SqlProvider();
            try
            {
                provider.GetMigrationSQL(session, true);
            }
            finally
            {
                dataBlock = provider.Block;
            }

How could I solve this?

richardjm

What you may need to do is have a complete TableMappings for your database (easily generated from the SchemaMappings) and exclude the tables you don't wish to compare from the table mappings. As in your code the tables don't exist at all when you come to generate the SQL it doesn't try to exclude foreign keys to/from the excluded tables.

Give that a go and hopefully it'll work a little better for you.

HTH

TheBatA

I tried something like this:

            TableMappings mappings = new TableMappings();
            schemaMappings = new SchemaMappings();
            schemaMappings.CreateMappings(db1, db2);
            mappings = schemaMappings.TableMappings;

            foreach (TableMapping tm in mappings)
            {
                if(tm.Obj1!=null)
                if (!ideLi("[dbo].[" + tm.Obj1.Name + "]")) // ideLi method chechs if table should be included
                {
                    tm.Include = false;
                }
                if(tm.Obj2!=null)
                    if (!ideLi("[dbo].[" + tm.Obj2.Name + "]"))
                    {
                        tm.Include = false;
                    }

            }
            session = new ComparisonSession();
            session.CompareDatabases(db1, db2, mappings);

but I still get the same result.

Is this correct syntax for excluding tables from data compare?

TheBatA

Is there any way I could drop all constraints before executing transaction and adding them at the end?

I tried a lot of ways solving problem, but I still get the same result.

richardjm

You need to make sure that you set the EngineDataCompareOptions on the SqlProvider before calling GenerateSQL. Make sure you add the option
to disable foreign keys.

session.Options.SqlOptions |= DropConstraintsAndIndexes;

TheBatA

Well, I tried this:

                SqlProvider provider = new SqlProvider();
                session.Options.SqlOptions |= SqlOptions.DropConstraintsAndIndexes;
                provider.GetMigrationSQL(session, true);

I get the same result!

Where should I call GenerateSQL? I don't see that option in provider.

I also tried to add

 | SqlOptions.DisableKeys | SqlOptions.UseTransactions;

TheBatA

I have one idea I'd might use solving this problem, but I don't know if it's possible.
Can I somehow compare all the tables and then save list of constraints to be dropped form a session or schemamappings? Can I add this list in list of constraints in new session/schemamapping which would contain only tables I need?

Can anybody describe how this stuff with constraints work in SQL Toolkit?

richardjm

OK I've just had to go back up to the top and re-read your original question. You say that SQL Data Compare works ok but as soon as you try to use SQL Packager you get an error. Is this SQL Packager UI or the toolkit? Can you post some sample code of your SQL Packager information?

I've just run a test internally about disabling foreign keys and it seems to work for not-included tables (i.e. if you choose to synchronize a table and another table has a foreign key reference to it we will disable that foreign key even though you're not synchronzing that table). Although the tables have to be in the comparison and then excluded from the synchronization - I suppose a little trick so that they don't actually compare any data would be to set a WHERE clause on those tables such that they will return no rows (e.g. 1=2) :twisted: .

DisableKeys in the important option - I got confuddled with our options.

HTH

richardjm

TheBatA wrote:

I have one idea I'd might use solving this problem, but I don't know if it's possible.
Can I somehow compare all the tables and then save list of constraints to be dropped form a session or schemamappings? Can I add this list in list of constraints in new session/schemamapping which would contain only tables I need?

Can anybody describe how this stuff with constraints work in SQL Toolkit?

That's good timing, I was just investigating and preparing my reply then

TheBatA

Hey, this works!

Error wasn't in packager, it was this error I got in application that was created by packager, about constraints.

Well, I know that there is a proof that 1 is equal to 2 (for extremely large values of 1 ), but fortunatelly it's not the case here!

Thanks for help!

acjagacjag

Inorder to write ideLi()method we need to read the sdc xml settings and has to compare in the code. Do you offer any class for that? How to go about this in the SQL Compare 7.1 API?

Regards,
Jagadeeswaran AC

Brian Donahue

If you're basing the table selection on an existing project file, I believe the "ReplayUserActions" method of the "project" object is what you should use. You pass in a SchemaMappings collections ByRef and this method should apply the user-defined mappings specified in the project file. This had appeared in v7 or maybe v7.1, so hopefully you have that version of SDK.

I hope this works for you.

acjagacjag

Brian Donahue wrote:

If you're basing the table selection on an existing project file, I believe the "ReplayUserActions" method of the "project" object is what you should use. You pass in a SchemaMappings collections ByRef and this method should apply the user-defined mappings specified in the project file. This had appeared in v7 or maybe v7.1, so hopefully you have that version of SDK.

I hope this works for you.

:Thank you very much.