ComparisonOptions.ForceBinaryCollation appears to be ignored

brandonagr

I am using the data compare SDK (assembly version 10.2.4.113) 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(
		redgateProject.SelectTableActions,
		redgateProject.DataSource1,
		redgateProject.DataSource2,
		ref mappings);
		
	Log.Debug("Options after ReplayUserActions {0}", mappings.Options.ComparisonOptions);

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

	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);
		}
		else
		{
			SqlProvider sp = new SqlProvider();
			migrationBlock = sp.GetMigrationSQL(session, true);
		}

		Log.Debug("Redgate compare complete");
	}

brandonagr

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

[config].[tbl_DataPoint_Meta] Row 1 type Different
*dataPointIdentifier    147983c2-e247-4abe-b889-84af3fd35c6e
versionTSUtc    2/3/2014 10:14:59 PM    <>      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 ={[LOAD_CI_DEC_MWH_17WXBZ031EKCP;Self;None;True;False;0;;;]} *{[LOAD_DIMF_DEC_PRICE_BC50V5CD787A;
Self;None;True;False;0;;;]};
//test2 <>      result ={[LOAD_CI_DEC_MWH_17WXBZ031EKCP;Self;None;True;False;0;;;]} *{[LOAD_DIMF_DEC_PRICE_BC50V5CD787A;
Self;None;True;False;0;;;]};
//tesT2

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

UPDATE [config].[tbl_DataPoint_Meta] SET [versionTSUtc]='2014-02-03 22:14:59.313', [notes]='test' WHERE [dataPointIdentifier]='147983c2-e247-4abe-b889-84af3fd35c6e' AND (
        [dataPointIdentifier] IN (
        select
        identifier
        from
        administrator.tbl_Migration_GuidIdentifiers
        where
        objectType = 'DataPoint'
        )
      )

brandonagr

Annnnnnnnnd the problem was not copying the session options onto the SqlProvider object, the following works as expected

SqlProvider sp = new SqlProvider();
sp.Options = session.Options;
migrationBlock = sp.GetMigrationSQL(session, true);

Brian Donahue

Hello,
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.