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

jw970170

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?

richardjm

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

jw970170

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

here is my 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);
                }

Brian Donahue

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.

jw970170

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

/*
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

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.