What are the challenges you face when working across database platforms? Take the survey

ComparisonOptions.ForceBinaryCollation appears to be ignored

brandonagrbrandonagr Posts: 4
I am using the data compare SDK (assembly version and it seems to completly ignore my setting of the ForceBinaryCollation option. The databases are case insensitive. I attempted to decompile what its doing in session.CompareDatabases and I can't even find where it considers that option at all, so I have no idea how it could work. Is there something special in the setup required to use that option?

This is what my code looks like that is executing the comparison, and it is not finding a different between two columns that is just case
using (Database sourceDb = new Database())
using (Database destDb = new Database())
	var sourceConnectionString = new SqlConnectionStringBuilder(_context.SourceDatabase);
	ConnectionProperties sourceConnection = new ConnectionProperties(sourceConnectionString.DataSource, sourceConnectionString.InitialCatalog);

	Log.Trace("Registering source database {0}", sourceConnection.DatabaseName);
	sourceDb.RegisterForDataCompare(sourceConnection, Options.Default);

	var destConnectionString = new SqlConnectionStringBuilder(_context.DestinationDatabase);
	ConnectionProperties destConnection = new ConnectionProperties(destConnectionString.DataSource, destConnectionString.InitialCatalog);

	Log.Trace("Registering destination database {0}", destConnection.DatabaseName);
	destDb.RegisterForDataCompare(destConnection, Options.Default);

	SchemaMappings mappings = new SchemaMappings();
	mappings.Options = redgateProject.DCOptions;
	mappings.Options.ComparisonOptions |= ComparisonOptions.ForceBinaryCollation;
	Log.Debug("Options were {0}", mappings.Options.ComparisonOptions);
	mappings.CreateMappings(sourceDb, destDb);
	var actionResult = RedGate.SQLDataCompare.Engine.DataCompareUserActions.ReplayUserActions(
		ref mappings);
	Log.Debug("Options after ReplayUserActions {0}", mappings.Options.ComparisonOptions);

	if (actionResult.Count > 0)
		throw new InvalidOperationException("Unable to setup UserActions: " +
				actionResult.Select(warning => string.Format("Msg: {0} Action: {1} {2}", warning.Message, warning.UserAction.action, warning.UserAction.data))

	ExecutionBlock migrationBlock = null;
	using (ComparisonSession session = new ComparisonSession())
		Log.Debug("Running redgate compare");

		session.Options = redgateProject.DCOptions;
		session.Options.ComparisonOptions |= ComparisonOptions.ForceBinaryCollation;
		Log.Debug("Options were {0}", session.Options.ComparisonOptions);
		session.CompareDatabases(sourceDb, destDb, mappings);

		if (session.TableDifferences.All(table => table.DifferencesSummary.DifferenceCount(Row.RowType.TotalDifferent) == 0))
			Log.Info("No differences detected using {0}", project);
			SqlProvider sp = new SqlProvider();
			migrationBlock = sp.GetMigrationSQL(session, true);

		Log.Debug("Redgate compare complete");


  • Options
    After comparing my code with the CompareSessionExample sample code, it appears that it is detecting the difference, but it's not generating the correct sql

    Here is the debug output from the TableDifferences.ResultsStore, note that it dose say expression column is <> due to the test2 vs testT2
    &#91;config&#93;.&#91;tbl_DataPoint_Meta&#93; Row 1 type Different
    *dataPointIdentifier    147983c2-e247-4abe-b889-84af3fd35c6e
    versionTSUtc    2/3/2014 10:14:59 PM    &lt;&gt;      2/3/2014 11:45:31 PM
    friendlyName    10-NERC Charge by Load  ==      10-NERC Charge by Load
    precision       8       ==      8
    milestoneIdentifier     e0a64993-c001-47ee-991e-e75e4bef563c    ==      e0a64993-c001-47ee-991e-e75e4bef563c
    expression      result =&#123;&#91;LOAD_CI_DEC_MWH_17WXBZ031EKCP;Self;None;True;False;0;;;&#93;&#125; *&#123;&#91;LOAD_DIMF_DEC_PRICE_BC50V5CD787A;
    //test2 &lt;&gt;      result =&#123;&#91;LOAD_CI_DEC_MWH_17WXBZ031EKCP;Self;None;True;False;0;;;&#93;&#125; *&#123;&#91;LOAD_DIMF_DEC_PRICE_BC50V5CD787A;

    but the sql generated by sp.GetMigrationSQL(session, true) doesn't include the expression column even though the ResultsStore shows it as different

    UPDATE &#91;config&#93;.&#91;tbl_DataPoint_Meta&#93; SET &#91;versionTSUtc&#93;='2014-02-03 22:14:59.313', &#91;notes&#93;='test' WHERE &#91;dataPointIdentifier&#93;='147983c2-e247-4abe-b889-84af3fd35c6e' AND &#40;
            &#91;dataPointIdentifier&#93; IN &#40;
            objectType = 'DataPoint'
  • Options
    Annnnnnnnnd the problem was not copying the session options onto the SqlProvider object, the following works as expected
    SqlProvider sp = new SqlProvider&#40;&#41;;
    sp.Options = session.Options;
    migrationBlock = sp.GetMigrationSQL&#40;session, true&#41;;
  • Options
    Brian DonahueBrian Donahue Posts: 6,590 Bronze 1
    Thanks for following up. You do indeed need to enforce the options consistently at the mappings, comparisonsession and provider to get the options applied in the mappings, results, and script creation.

    I'm glad it's all sorted out.
Sign In or Register to comment.