How can I stop SDC from adding comments to sql script?

• 
  Hi
  
  You can achieve this by setting the TableMappings.Options in the following manner:
  

  mappings.Options = new EngineDataCompareOptions( 
                  MappingOptions.Default, 
                  ComparisonOptions.Default, 
                  SqlOptions.OutputComments ^ SqlOptions.OutputCommentHeader ^ SqlOptions.Default);
  

  
  You can see how I'm using the above code in the following program
  

  using System;
  using RedGate.SQL.Shared;
  using RedGate.SQLCompare.Engine;
  using RedGate.SQLDataCompare.Engine;
  
  namespace SQLDataCompareCodeSnippets
  {
      public class SqlProviderExample
      {
          public static void Main()
          {
              RunExample();
              Console.Read();
          }
  
          public static void RunExample()
          {
              Database db1 = new Database();
              Database db2 = new Database();
  
              db1.RegisterForDataCompare(new ConnectionProperties(".", "Test1"), Options.Default);
              db2.RegisterForDataCompare(new ConnectionProperties(".", "Test2"), Options.Default);
  
              // Create the mappings between the two databases
              TableMappings mappings = new TableMappings();
              mappings.CreateMappings(db1.Tables, db2.Tables);
  
              //
              //Setup options so as to exlude the comment header and general comments from the scripts
              //
              mappings.Options = new EngineDataCompareOptions(
                  MappingOptions.Default,
                  ComparisonOptions.Default,
                  SqlOptions.OutputComments ^ SqlOptions.OutputCommentHeader ^ SqlOptions.Default);
           
  
              using (ComparisonSession session = new ComparisonSession())
              {
                  //
                  // Remember to set up the session options
                  //
                  session.Options = mappings.Options;
                  session.CompareDatabases(db1, db2, mappings);
  
                  // now get the ExecutionBlock containing the SQL
                  // we want to run this on Test2 so we pass on true as the second parameter
                  SqlProvider provider = new SqlProvider();
                  //
                  // Also rememeber to set up the provider options
                  //
                  provider.Options = session.Options;
                  ExecutionBlock block;
                  try
                  {
                      block = provider.GetMigrationSQL(session, true);
  
                      Console.WriteLine("The synchronization SQL contains {0} lines in {1} batches", block.LineCount, block.BatchCount);
  
                      // if the ExecutionBlock was very large this could cause memory problems
                      Console.WriteLine("The SQL to be run is:");
                      Console.WriteLine(block.GetString());
  
                      // we can access the SQL in a memory efficient manner by accessing the underlying stream
                      // FileStream stream=block.GetFileStream();
  
                      // run the SQL ( commented out by default )
                      // BlockExecutor executor = new BlockExecutor();
                      // executor.ExecuteBlock(block, ".", "Test2");
                  }
                  finally
                  {
                      block = provider.Block;
                      if (block != null)
                      {
                          block.Dispose();    // dispose of the objects to delete temporary files
                      }
                  }
              }
              db1.Dispose();
              db2.Dispose();
          }
      }
  }
  

  
  Hope this helps
  Chris

  Chris Spencer November 05, 2007 11:12

  - 0 +
• 
  I'm using vb.net so I assume I can do it like this:
  
  Dim mappings As New TableMappings
  
  mappings.Options.SqlOptions = SqlOptions.OutputComments Xor SqlOptions.OutputCommentHeader Xor SqlOptions.Default
  
  Correct?
  
  Thank you.

  RBohannon November 05, 2007 12:23

  - 0 +
• 
  Sorry I assumed C#. My knowledge of Vb.Net isn't the best but to my eyes your code should work.
  
  Interestingly when I disassembled my test assembly with Reflector and chose to display it as VB I got the following -
  

  mappings.Options = New EngineDataCompareOptions(MappingOptions.Default, ComparisonOptions.Default, (SqlOptions.ReseedIdentity Or (SqlOptions.UseTransactions Or SqlOptions.DisableKeys)))
  

  
  ...maybe my code was optimised by the compiler when I generated the assembly but it seems to use the logical Or rather than Xor. Personally I'd try the Xor first as this seems to work for me in c#.
  
  
  Regards
  Chris

  Chris Spencer November 05, 2007 13:24

  - 0 +
• 
  I was wondering about that... typically you use a bitwise OR to join together different members of an enumeration object. In C#, this is symbolized by the vertical bar | whereas a logical OR is a double vertical bar ||. Exclusive or ^ will set the particular option in the enumeration if it is not already set, but unset it if it has already been set (1 XOR 0=1; 1 XOR 1=0), so maybe this isn't what you want to do.
  
  The Visual Basic language doesn't discriminate between a logical and a bitwise OR, in either case, the VB operator is simply Or and the VB compiler is meant to determine whether to use logical or bitwise depending on the operation.

  Brian Donahue November 06, 2007 04:48

  - 0 +
• 
  I'm still getting comments in the text file. By the way, I added "UseTransactions" to the xor string because we also wanted to turn off that setting. So the line of code became:
  
  mappings.Options.SqlOptions = SqlOptions.OutputComments Xor SqlOptions.UseTransactions Xor SqlOptions.OutputCommentHeader Xor SqlOptions.Default
  
  This is equivalent to
  
  mappings.Options.SqlOptions = SqlOptions.DisableKeys
  
  because DisableKeys is the only default setting I'm not turning off. I confirmed in the watch window that my xor string is being evaluated to just "DisableKeys". But the program seems to be ignoring the SqlOptions altogether and inserting all the comments and transaction statements.
  
  Any idea why the program is ignoring SqlOptions?
  
  Thanks.

  RBohannon November 06, 2007 12:38

  - 0 +
• 
  It seems like you have set the SqlOptions correctly.
  
  In my code example above there are some important assignments further down that ensure that the SqlOptions in TableMappings.Options are also used by the SQLProvider object.
  
  It does it in 2 steps. Firstly:

  session.Options = mappings.Options;
  

  and then:

  provider.Options = session.Options;
  

  
  You might be able to get away with setting it directly e.g.

  mappings.Options.SqlOptions = SqlOptions.DisableKeys 
  provider.Options.SqlOptions = mappings.Options.SqlOptions
  

  
  Whatever technique you choose, I suspect that the reason that you are getting comments still is that you need to set up the options for the SqlProvider object.

  Chris Spencer November 06, 2007 12:53

  - 0 +
• 
  I missed that step. I set neither session.options nor provider.options. SqlOptions is being evaluated to Default for both in my watch window.
  
  I will make the change and let you know if that fixes it.

  RBohannon November 06, 2007 13:03

  - 0 +
• 
  Issue resolved. Thank you.

  RBohannon November 06, 2007 14:38

  - 0 +
• 
  That's excellent news. Thanks for letting us know
  
  Regards
  Chris

  Chris Spencer November 06, 2007 15:33

  - 0 +

Add comment

Please sign in to leave a comment.