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

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

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

Postby jw970170 » Wed Nov 07, 2007 4:54 pm

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?
jw970170
 
Posts: 12
Joined: Thu Nov 01, 2007 5:11 pm

Postby richardjm » Wed Nov 07, 2007 5:26 pm

I think your query has been answered elsewhere.

Have a look at this forum thread...

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

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 jw970170 » Fri Nov 09, 2007 6:13 pm

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
jw970170
 
Posts: 12
Joined: Thu Nov 01, 2007 5:11 pm

Postby jw970170 » Fri Nov 09, 2007 6:14 pm

here is my code

Code: Select all
                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);
                }
jw970170
 
Posts: 12
Joined: Thu Nov 01, 2007 5:11 pm

Postby Brian Donahue » Fri Nov 09, 2007 6:44 pm

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

Postby jw970170 » Fri Nov 09, 2007 6:55 pm

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: Select all
/*
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
jw970170
 
Posts: 12
Joined: Thu Nov 01, 2007 5:11 pm

Postby jw970170 » Fri Nov 09, 2007 8:25 pm

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.
jw970170
 
Posts: 12
Joined: Thu Nov 01, 2007 5:11 pm


Return to SQL Toolkit Previous Versions

Who is online

Users browsing this forum: No registered users and 0 guests