ComparisonOptions.ForceBinaryCollation appears to be ignored

brandonagrbrandonagr Posts: 4
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");
	}

Comments

  • 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;
    Self;None;True;False;0;;;&#93;&#125;;
    //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;
    Self;None;True;False;0;;;&#93;&#125;;
    //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 &#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;
            select
            identifier
            from
            administrator.tbl_Migration_GuidIdentifiers
            where
            objectType = 'DataPoint'
            &#41;
          &#41;
    
  • 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;;
    
  • Brian DonahueBrian Donahue Posts: 6,590 Bronze 1
    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.
Sign In or Register to comment.