Updating databases - foreign keys problem

Forum for users of SQL Toolkit 3,4,and 5

Updating databases - foreign keys problem

Postby TheBatA » Tue Sep 25, 2007 11:22 am

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:
Code: Select all
            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?
TheBatA
 
Posts: 12
Joined: Tue Sep 25, 2007 8:37 am
Location: Belgrade

Postby richardjm » Tue Sep 25, 2007 11:33 am

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
Richard Mitchell
Project Manager
Red Gate Software Ltd
richardjm
 
Posts: 395
Joined: Fri May 20, 2005 9:44 am
Location: Red Gate Software Ltd

Postby TheBatA » Tue Sep 25, 2007 12:11 pm

I tried something like this:

Code: Select all
            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
 
Posts: 12
Joined: Tue Sep 25, 2007 8:37 am
Location: Belgrade

Postby TheBatA » Tue Sep 25, 2007 12:57 pm

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.
TheBatA
 
Posts: 12
Joined: Tue Sep 25, 2007 8:37 am
Location: Belgrade

Postby richardjm » Tue Sep 25, 2007 1:43 pm

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.

Code: Select all
session.Options.SqlOptions |= DropConstraintsAndIndexes;
Richard Mitchell
Project Manager
Red Gate Software Ltd
richardjm
 
Posts: 395
Joined: Fri May 20, 2005 9:44 am
Location: Red Gate Software Ltd

Postby TheBatA » Tue Sep 25, 2007 2:06 pm

Well, I tried this:
Code: Select all
                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
Code: Select all
 | SqlOptions.DisableKeys | SqlOptions.UseTransactions;
TheBatA
 
Posts: 12
Joined: Tue Sep 25, 2007 8:37 am
Location: Belgrade

Postby TheBatA » Wed Sep 26, 2007 9:01 am

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?
TheBatA
 
Posts: 12
Joined: Tue Sep 25, 2007 8:37 am
Location: Belgrade

Postby richardjm » Wed Sep 26, 2007 9:05 am

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
Richard Mitchell
Project Manager
Red Gate Software Ltd
richardjm
 
Posts: 395
Joined: Fri May 20, 2005 9:44 am
Location: Red Gate Software Ltd

Re:

Postby richardjm » Wed Sep 26, 2007 9:08 am

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 :D
Richard Mitchell
Project Manager
Red Gate Software Ltd
richardjm
 
Posts: 395
Joined: Fri May 20, 2005 9:44 am
Location: Red Gate Software Ltd

Postby TheBatA » Wed Sep 26, 2007 1:47 pm

Hey, this works! :D

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 :D ), but fortunatelly it's not the case here!

Thanks for help!
TheBatA
 
Posts: 12
Joined: Tue Sep 25, 2007 8:37 am
Location: Belgrade

How to read TableActions xml setting from the sdc file

Postby acjagacjag » Thu Jan 29, 2009 8:37 am

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
acjagacjag
 
Posts: 7
Joined: Thu Jan 29, 2009 8:30 am

Postby Brian Donahue » Thu Jan 29, 2009 10:35 am

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.
Brian Donahue
 
Posts: 6670
Joined: Mon Aug 23, 2004 10:48 am

Re:

Postby acjagacjag » Thu Jan 29, 2009 12:05 pm

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.
acjagacjag
 
Posts: 7
Joined: Thu Jan 29, 2009 8:30 am


Return to SQL Toolkit Previous Versions

Who is online

Users browsing this forum: No registered users and 1 guest