What are the challenges you face when working across database platforms? Take the survey
Options

Options are not reflected in the generated script

dshahdshah Posts: 4
Hello,
I am using the following code to generate a data compare script. Script generation works fine for the most part. My problem is I am unable to have Options reflected in the script.

Couple of examples of what my expectation is and how the generated script is different.
SqlOptions.ForceCheck option is turned ON, and I still see a bunch of constraints with the "WITH NOCHECK" clause. I was expecting NO "WITH NOCHECK" clause in the output, if I had the ForceCheck ON.
SqlOptions.DontUseTransactions option is turned ON, and I was expecting to see NO "BEGIN TRANSACTION" in the script, but it was there. (This option was only turned ON for testing purposes).

Am I missing something here?

Thanks in advance!
            using (Database db1 = new Database(), db2 = new Database())
            {
                var username = "xxx";
                var sourceUserName = username;
                var destinationUserName = username;

                if (!string.IsNullOrWhiteSpace(username) && username.Contains("{0}"))
                {
                    sourceUserName = String.Format(username, SourceDatabase);
                    destinationUserName = String.Format(username, DestinationDatabase);
                }

                var password = "yyy";

                db1.RegisterForDataCompare(new ConnectionProperties(SourceServer, SourceDatabase, sourceUserName, password), Options.Default);
                db2.RegisterForDataCompare(new ConnectionProperties(DestinationServer, DestinationDatabase, destinationUserName, password), Options.Default);
                
                var tableMappings = new TableMappings();
                tableMappings.CreateMappings(db2.Tables, db1.Tables);

                tableMappings.Options = new EngineDataCompareOptions(
                    MappingOptions.IncludeTimestamps | MappingOptions.Default,
                    ComparisonOptions.TrimTrailingSpaces | ComparisonOptions.Default,
                    SqlOptions.DontUseTransactions | SqlOptions.ForceCheck); 

                using (var session = new ComparisonSession())
                {
                    session.Options = tableMappings.Options;
                    session.CompareDatabases(db2, db1, tableMappings);

                    var provider = new SyncProvider(); 
                    provider.Options = session.Options;
                    ExecutionBlock block;

                    try
                    {
                        block = provider.GetMigrationSQL(session);
                        output = block.GetString();
                    }
                    catch (Exception ex)
                    {
                        output = ex.Message;
                        //todo: add code to continue with the loop
                    }
                    finally
                    {
                        block = provider.Block;
                        if (block != null)
                        {
                            block.Dispose(); // dispose of the objects to delete temporary files 
                        }
                    }
                }
            }

Comments

  • Options
    Thanks for your post.

    The options should be behaving as you expect, so I tried to test your code.

    I couldn't get it to work so I ended up testing the following:
    using (Database db1 = new Database(), db2 = new Database())
                {
                    
    
                    db1.RegisterForDataCompare(new ConnectionProperties(Program.DevServerName, Program.DevDatabaseName), Options.Default);
                    db2.RegisterForDataCompare(new ConnectionProperties(Program.LiveServerName, Program.LiveDatabaseName), Options.Default);
    
                    var tableMappings = new TableMappings();
                    tableMappings.CreateMappings(db2.Tables, db1.Tables);
    
                    tableMappings.Options = new EngineDataCompareOptions(
                        MappingOptions.IncludeTimestamps | MappingOptions.Default,
                        ComparisonOptions.TrimTrailingSpaces | ComparisonOptions.Default,
                        SqlOptions.DontUseTransactions | SqlOptions.ForceCheck);
    
                    using (var session = new ComparisonSession())
                    {
                        session.Options = tableMappings.Options;
                        session.CompareDatabases(db2, db1, tableMappings);
    
                        SqlProvider provider = new SqlProvider();
                        provider.Options = session.Options;
                       
    
                        try
                        {
                            ExecutionBlock block = provider.GetMigrationSQL(session, true);
                            Console.WriteLine("The SQL to be run is:");
                            Console.WriteLine(block.GetString());
                        }
                        catch (Exception ex)
                        {
                            Console.WriteLine(ex.Message);
                            //todo: add code to continue with the loop 
                        }
                        finally
                        {
                            ExecutionBlock block = provider.Block;
                            if (block != null)
                            {
                                block.Dispose(); // dispose of the objects to delete temporary files 
                            }
                        }
                    }
                }
    

    This seems to work ok for me, so maybe the difference is you using the SyncProvider() class instead of sqlprovider()
    Chris
Sign In or Register to comment.