Red Gate forums :: View topic - Updating databases - foreign keys problem
Return to www.red-gate.com RSS Feed Available

Search  | Usergroups |  Profile |  Messages |  Log in  Register 
Go to product documentation
SQL Toolkit Previous Versions
SQL Toolkit Previous Versions forum

Updating databases - foreign keys problem

Search in SQL Toolkit Previous Versions forum
Post new topic   Reply to topic
Jump to:  
Author Message
TheBatA



Joined: 25 Sep 2007
Posts: 12
Location: Belgrade

PostPosted: Tue Sep 25, 2007 11:22 am    Post subject: Updating databases - foreign keys problem Reply with quote

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:

            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?
Back to top
View user's profile Send private message
richardjm



Joined: 20 May 2005
Posts: 383
Location: Red Gate Software Ltd

PostPosted: Tue Sep 25, 2007 11:33 am    Post subject: Reply with quote

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
Back to top
View user's profile Send private message Send e-mail
TheBatA



Joined: 25 Sep 2007
Posts: 12
Location: Belgrade

PostPosted: Tue Sep 25, 2007 12:11 pm    Post subject: Reply with quote

I tried something like this:

Code:

            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?
Back to top
View user's profile Send private message
TheBatA



Joined: 25 Sep 2007
Posts: 12
Location: Belgrade

PostPosted: Tue Sep 25, 2007 12:57 pm    Post subject: Reply with quote

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.
Back to top
View user's profile Send private message
richardjm



Joined: 20 May 2005
Posts: 383
Location: Red Gate Software Ltd

PostPosted: Tue Sep 25, 2007 1:43 pm    Post subject: Reply with quote

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:

session.Options.SqlOptions |= DropConstraintsAndIndexes;

_________________
Richard Mitchell
Project Manager
Red Gate Software Ltd
Back to top
View user's profile Send private message Send e-mail
TheBatA



Joined: 25 Sep 2007
Posts: 12
Location: Belgrade

PostPosted: Tue Sep 25, 2007 2:06 pm    Post subject: Reply with quote

Well, I tried this:
Code:

                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:

 | SqlOptions.DisableKeys | SqlOptions.UseTransactions;
Back to top
View user's profile Send private message
TheBatA



Joined: 25 Sep 2007
Posts: 12
Location: Belgrade

PostPosted: Wed Sep 26, 2007 9:01 am    Post subject: Reply with quote

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?
Back to top
View user's profile Send private message
richardjm



Joined: 20 May 2005
Posts: 383
Location: Red Gate Software Ltd

PostPosted: Wed Sep 26, 2007 9:05 am    Post subject: Reply with quote

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 Evil .

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

HTH
_________________
Richard Mitchell
Project Manager
Red Gate Software Ltd
Back to top
View user's profile Send private message Send e-mail
richardjm



Joined: 20 May 2005
Posts: 383
Location: Red Gate Software Ltd

PostPosted: Wed Sep 26, 2007 9:08 am    Post subject: Re: Reply with quote

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 Very Happy
_________________
Richard Mitchell
Project Manager
Red Gate Software Ltd
Back to top
View user's profile Send private message Send e-mail
TheBatA



Joined: 25 Sep 2007
Posts: 12
Location: Belgrade

PostPosted: Wed Sep 26, 2007 1:47 pm    Post subject: Reply with quote

Hey, this works! Very Happy

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

Thanks for help!
Back to top
View user's profile Send private message
acjagacjag



Joined: 29 Jan 2009
Posts: 7

PostPosted: Thu Jan 29, 2009 8:37 am    Post subject: How to read TableActions xml setting from the sdc file Reply with quote

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
Back to top
View user's profile Send private message
Brian Donahue



Joined: 23 Aug 2004
Posts: 6643

PostPosted: Thu Jan 29, 2009 10:35 am    Post subject: Reply with quote

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.
Back to top
View user's profile Send private message
acjagacjag



Joined: 29 Jan 2009
Posts: 7

PostPosted: Thu Jan 29, 2009 12:05 pm    Post subject: Re: Reply with quote

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.
Back to top
View user's profile Send private message
Display posts from previous:   
Reply to topic All times are GMT + 1 Hour
Page 1 of 1

 
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group