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

RBohannonRBohannon Posts: 25
Each night, our application uses the SQL Data Compare API to create a syncronization SQL script which is then sent from the client site to our home office.

We are trying to minimize the file size of these SQL scripts for our clients so we would like to prevent SQL Data Compare from adding any comments to the script. Is there a built-in way to do this?

Thank you.

Comments

  • 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
    Test Engineer
    Red Gate
  • 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.
  • 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
    Test Engineer
    Red Gate
  • Brian DonahueBrian Donahue Posts: 6,590 Bronze 1
    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.
  • 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.
  • 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
    Test Engineer
    Red Gate
  • 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.
  • Issue resolved. Thank you.
  • That's excellent news. Thanks for letting us know :D

    Regards
    Chris
    Chris Spencer
    Test Engineer
    Red Gate
Sign In or Register to comment.