Red Gate forums :: View topic - How do I set up a custom comparison key with the API
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

How do I set up a custom comparison key with the API

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



Joined: 01 Nov 2007
Posts: 12

PostPosted: Wed Nov 07, 2007 4:54 pm    Post subject: How do I set up a custom comparison key with the API Reply with quote

Hi,

I need to set up a custom comparison key with the API but I can't figure out how to do it? Any ideas?
Back to top
View user's profile Send private message
richardjm



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

PostPosted: Wed Nov 07, 2007 5:26 pm    Post subject: Reply with quote

I think your query has been answered elsewhere.

Have a look at this forum thread...

http://www.red-gate.com/MessageBoard/viewtopic.php?t=4078

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



Joined: 01 Nov 2007
Posts: 12

PostPosted: Fri Nov 09, 2007 6:13 pm    Post subject: Reply with quote

Thanks for your help. That worked...sort of. I added the custom key, and then did session.CompareDatabases(dbLocal, dbMaster, mappings);

I set up the table with the custom mapping so that one of the rows in one database had an update in it. This update was detected in the differencesummary. I set the SqlSynchronization to Update and Add and then proceded to first update the master db from the local, and then update the local from the master (so new rows in both databases are added to each respective database)

block = provider.GetMigrationSQL(session, true);
executor.ExecuteBlock(block, MasterServer, MasterDatabase, false, MasterUserName, MasterPassword);

block2 = provider.GetMigrationSQL(session, false);
executor.ExecuteBlock(block2, ClientServer, ClientDatabase, false, ClientUserName, ClientPassword);

The problem is, the update is not happening. Each time it says synchronization is complete and yet the table does not get updated and the difference summery always shows 1 difference. When I do it from the desktop tool, it updates properly. Any idea what is wrong? There is binary data in the table
Back to top
View user's profile Send private message
jw970170



Joined: 01 Nov 2007
Posts: 12

PostPosted: Fri Nov 09, 2007 6:14 pm    Post subject: Reply with quote

here is my code

Code:

                dbLocal = new Database();
                dbMaster = new Database();

                dbLocal.RegisterForDataCompare(new ConnectionProperties(ClientServer, ClientDatabase, ClientUserName, ClientPassword));
                dbMaster.RegisterForDataCompare(new ConnectionProperties(MasterServer, MasterDatabase, MasterUserName, MasterPassword));

                //Compare the DBs
               // SchemaMappings mappings = new SchemaMappings();
                TableMappings mappings = new TableMappings();
                mappings.CreateMappings(dbLocal.Tables, dbMaster.Tables);

                //TableMappings tmappings = new TableMappings();

                TableMapping tableMapping = (TableMapping)mappings.Join(dbLocal.Tables["[dbo].[GIS_RoadSections]"], dbMaster.Tables["[dbo].[GIS_RoadSections]"]);

                // Set the custom comparison key for the table
                tableMapping.MatchingMappings.Clear();
                tableMapping.MatchingMappings.Add(tableMapping.FieldMappings["RoadSectionsID"]);
                tableMapping.RefreshMappingStatus();

                //remove the old mapping and add the new one
                mappings.Remove(mappings["[dbo].[GIS_RoadSections]"]);//["[dbo].[GIS_RoadSections]"];
                //tmappings.Add(tableMapping);
                mappings.Add(tableMapping);
               
                session = new ComparisonSession();
                session.Status += new StatusEventHandler(StatusCallback);
                //session.Database1.AsymmetricKeys.
                //session.Options.ComparisonOptions = ComparisonOptions.UseChecksumComparison;//.SqlOptions == SqlOptions.
               
                session.CompareDatabases(dbLocal, dbMaster, mappings);

                for (int i = 0; i < session.TableDifferences.Count; i++)
                {
                    session.TableDifferences[i].SqlSynchronization = SqlSynchronization.UpdateSql | SqlSynchronization.AddSql;
                }

                BlockExecutor executor = new BlockExecutor();
                SqlProvider provider = new SqlProvider();

                executor.Status += new StatusEventHandler(ExecutorStatusCallback);

                //update master from client
                if (UpdateMasterFromLocal)
                {
                    block = provider.GetMigrationSQL(session, true);
                    executor.ExecuteBlock(block, MasterServer, MasterDatabase, false, MasterUserName, MasterPassword);
                }

                //update client from master
                if (UpdateLocalFromMaster)
                {
                    block2 = provider.GetMigrationSQL(session, false);
                    executor.ExecuteBlock(block2, ClientServer, ClientDatabase, false, ClientUserName, ClientPassword);
                }
Back to top
View user's profile Send private message
Brian Donahue



Joined: 23 Aug 2004
Posts: 6649

PostPosted: Fri Nov 09, 2007 6:44 pm    Post subject: Reply with quote

Are the updates for the GIS_RoadSections table present in the migration SQL code? Maybe converting the block to a string (.GetString()) and seeing if any updates are present for that table would help rule your code out as the cause and possibly get you looking into SQL Server as the cause.

If the expected update statements don't appear for the table, you may want to check the status property of the table's mapping right after calling RefreshMappingStatus. There may be a problem matching the RoadSectionsID column as a suitable primary key.
Back to top
View user's profile Send private message
jw970170



Joined: 01 Nov 2007
Posts: 12

PostPosted: Fri Nov 09, 2007 6:55 pm    Post subject: Reply with quote

The update statement appears to have the correct information in it. I moved the statement to a query file in management studio and ran it and it updated properly. So the error seems to have something to do with the ExecuteBlock method...I've shortened the ShapeData just cause is is binary but this is basically what the block output

Code:

/*
Script created by SQL Data Compare version 6.0.0.776 from Red Gate Software Ltd at 09/11/2007 12:43:51 PM

Run this script on (local)\SQLEXPRESS.mrComplianceSub

This script will make changes to (local)\SQLEXPRESS.mrComplianceSub to make it the same as AKA-WK29\SQLEXPRESS.mrCompliance

Note that this script will carry out all DELETE commands for all tables first, then all the UPDATES and then all the INSERTS
It will disable foreign key constraints at the beginning of the script, and re-enable them at the end
*/
SET NUMERIC_ROUNDABORT OFF
GO
SET XACT_ABORT, ANSI_PADDING, ANSI_WARNINGS, CONCAT_NULL_YIELDS_NULL, ARITHABORT, QUOTED_IDENTIFIER, ANSI_NULLS, NOCOUNT ON
GO
-- Pointer used for text / image updates. This might not be needed, but is declared here just in case
DECLARE @pv binary(16)

BEGIN TRANSACTION

-- Update 1 row in [dbo].[GIS_RoadSections]
EXEC(N'UPDATE [dbo].[GIS_RoadSections] SET [ShapeData]=0x030, [UniqueID]=''221c6504-11af-4a63-93ec-b12e7449d13c'' WHERE [RoadSectionsID]=''67923931-af74-484c-85c2-996288a6ff8c''')

COMMIT TRANSACTION
GO
Back to top
View user's profile Send private message
jw970170



Joined: 01 Nov 2007
Posts: 12

PostPosted: Fri Nov 09, 2007 8:25 pm    Post subject: Reply with quote

ok, there error was that I was changing it when I did the first executer but then I changed it back when I did the second. I need to do a session compare inbetween.

Thanks for your help.
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