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

RBohannon

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.

Chris Spencer

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

RBohannon

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.

Chris Spencer

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

Brian Donahue

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.

RBohannon

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.

Chris Spencer

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.

RBohannon

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

Issue resolved. Thank you.

Chris Spencer

That's excellent news. Thanks for letting us know

Regards
Chris