Red Gate forums :: View topic - ComparisonOptions.ForceBinaryCollation appears to be ignored
Return to www.red-gate.com RSS Feed Available

Search  | Usergroups |  Profile |  Messages |  Log in  Register 
Go to product documentation
SQL Comparison SDK 10
SQL Comparison SDK 10 forum

ComparisonOptions.ForceBinaryCollation appears to be ignored

Search in SQL Comparison SDK 10 forum
Post new topic   Reply to topic
Jump to:  
Author Message
brandonagr



Joined: 04 Feb 2014
Posts: 3

PostPosted: Tue Feb 04, 2014 1:36 am    Post subject: ComparisonOptions.ForceBinaryCollation appears to be ignored Reply with quote

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:
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");
   }
Back to top
View user's profile Send private message
brandonagr



Joined: 04 Feb 2014
Posts: 3

PostPosted: Tue Feb 04, 2014 1:54 am    Post subject: Reply with quote

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:
[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:
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'
        )
      )
Back to top
View user's profile Send private message
brandonagr



Joined: 04 Feb 2014
Posts: 3

PostPosted: Tue Feb 04, 2014 2:05 am    Post subject: Reply with quote

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

Code:
SqlProvider sp = new SqlProvider();
sp.Options = session.Options;
migrationBlock = sp.GetMigrationSQL(session, true);
Back to top
View user's profile Send private message
Brian Donahue



Joined: 23 Aug 2004
Posts: 6646

PostPosted: Wed Feb 05, 2014 2:10 pm    Post subject: Reply with quote

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.
Back to top
View user's profile Send private message
Display posts from previous:   
Reply to topic All times are GMT + 1 Hour
Page 1 of 1

 
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group