ComparisonOptions.ForceBinaryCollation appears to be ignored

Automate and integrate using the SQL comparison API

Moderators: Chris Auckland, David Atkinson, Michelle Taylor, chengvoon.tong

ComparisonOptions.ForceBinaryCollation appears to be ignored

Postby brandonagr » Tue Feb 04, 2014 1:36 am

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

Code: Select all
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
 
Posts: 3
Joined: Tue Feb 04, 2014 1:24 am

Postby brandonagr » Tue Feb 04, 2014 1:54 am

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

Code: Select all
[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


Code: Select all
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
 
Posts: 3
Joined: Tue Feb 04, 2014 1:24 am

Postby brandonagr » Tue Feb 04, 2014 2:05 am

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

Code: Select all
SqlProvider sp = new SqlProvider();
sp.Options = session.Options;
migrationBlock = sp.GetMigrationSQL(session, true);
brandonagr
 
Posts: 3
Joined: Tue Feb 04, 2014 1:24 am

Postby Brian Donahue » Wed Feb 05, 2014 2:10 pm

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.
Brian Donahue
 
Posts: 6670
Joined: Mon Aug 23, 2004 10:48 am


Return to SQL Comparison SDK 10

Who is online

Users browsing this forum: No registered users and 0 guests