| Author |
Message |
jw970170
Joined: 01 Nov 2007
Posts: 12
|
 Posted: Wed Nov 07, 2007 4:54 pm Post subject: How do I set up a custom comparison key with the API |
 |
|
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 |
|
 |
richardjm
Joined: 20 May 2005
Posts: 359
Location: Red Gate Software Ltd
|
|
| Back to top |
|
|
jw970170
Joined: 01 Nov 2007
Posts: 12
|
| Posted: Fri Nov 09, 2007 6:13 pm Post subject: |
|
|
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 |
|
|
jw970170
Joined: 01 Nov 2007
Posts: 12
|
| Posted: Fri Nov 09, 2007 6:14 pm Post subject: |
|
|
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 |
|
|
Brian Donahue
Joined: 23 Aug 2004
Posts: 6369
Location: Red Gate Software
|
| Posted: Fri Nov 09, 2007 6:44 pm Post subject: |
|
|
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
Technical Support
Red Gate Software Ltd.
44 (0)870 160 0037 ext 8521
US and CAN 1-866-RED GATE ext 8521 |
|
| Back to top |
|
|
jw970170
Joined: 01 Nov 2007
Posts: 12
|
| Posted: Fri Nov 09, 2007 6:55 pm Post subject: |
|
|
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 |
|
|
jw970170
Joined: 01 Nov 2007
Posts: 12
|
| Posted: Fri Nov 09, 2007 8:25 pm Post subject: |
|
|
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 |
|
|
|
