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

How the SQL compare include/exclude object types works

ToddYToddY Posts: 4 New member
Hi, 

I am trying to call SQL Compare to sync 2 databases with command line, such as "/include:table /include:schema /include:view /include:Identical"

However, it only synced tables and schema, but the views are not synced. I inherited the code and I guess somewhere there is a definition of exclusion and that exclusion overrides the "/include:view". but I cannot find where the exclusion is defined. 

Any suggestions of how to include the sync of view?
Tagged:

Answers

  • Options
    Can you share the full command line script?

    Thanks.
    Kind regards

    Tianjiao Li | Redgate Software
    Have you visited our Help Center?
  • Options
    ToddYToddY Posts: 4 New member
    Tianjiao,

    Thanks for looking at my question. 

    The full command line script was dynamically generated from SSIS's C# code, and it will look like this: 

    /server1: S1 /username1: u1 /password1: p1 /database1: D1 /server2: S2 /username2: u2 /password2: p2 /database2: D2 /force /synchronize /options: DecrypPost2KEncrypted,IgnoreFillfactor,IgnoreWhleSpace,Ignfore fileGroup,IgnoreUserProperties,IgnoreWithElementOrders,IgnoreDatabaseAndServerName /include:userdefinedType /include:table /include:schema /include:view /include:identical /loglevel:Verbose /Out:C:\Temp\serverDB_output.txt

    It works on syncing table and schema, but not view. Really strange.  Please help.

    Thanks again.

    Todd Yu
  • Options
    Hi Todd,

    Thanks for sharing the command script, however, I wasn't able to reproduce it with my database. 

    If you use the UI, with the same options, do you see the views?
    Kind regards

    Tianjiao Li | Redgate Software
    Have you visited our Help Center?
  • Options
    ToddYToddY Posts: 4 New member
    Hi Tianjian, thank you for your prompt reply. 

    I am new to SQL Compare. From the UI, I am seeing many options under the "Behavior" and "Ignore" when I open a SCP file. but where I can see the "include" and "exclude" options?

    Thanks,
  • Options
    ToddYToddY Posts: 4 New member
    Hi Tianjiao, 

    Here is the SSIS code that got called: 

    public void Main()
    {
    // TODO: Add your code here
                try
                {
                    string[] dblist = Dts.Variables["$Package::DBList"].Value.ToString().Split(new Char[] { ';' });
                    bool nope = false;
                    
                    foreach (string db in dblist)
                    {
                        StringBuilder sb = new StringBuilder();
                        sb.Append(" /server1:");
                        sb.Append(Dts.Variables["$Package::SourceServer"].Value.ToString());
                        sb.Append(" /username1:");
                        sb.Append(Dts.Variables["$Package::SourceServerUserName"].Value.ToString());
                        sb.Append(" /password1:");
                        sb.Append(Dts.Variables["$Package::SourceServerPassword"].Value.ToString());
                        sb.Append(" /database1:");
                        sb.Append(db);
                        sb.Append(" /server2:");
                        sb.Append(Dts.Variables["$Package::DestinationServer"].Value.ToString());
                        sb.Append(" /username2:");
                        sb.Append(Dts.Variables["$Package::DestinationServerUserName"].Value.ToString());
                        sb.Append(" /password2:");
                        sb.Append(Dts.Variables["$Package::DestinationServerPassword"].Value.ToString());
                        sb.Append(" /database2:");
                        sb.Append(db);
                        sb.Append(" /force /synchronize /Options:DecryptPost2KEncryptedObjects,IgnoreFillFactor,IgnoreWhiteSpace,IgnoreFileGroups,IgnoreUserProperties,IgnoreWithElementOrder,IgnoreDatabaseAndServerName ");
                        // bring in sync flags
                        sb.Append(Dts.Variables["$Package::SyncingFlags"].Value.ToString());
                        sb.Append(" /logLevel:Verbose");

                        sb.Append(" /Out:" + Dts.Variables["$Package::LoggingDirectory"].Value.ToString() + Dts.Variables["$Package::DestinationServer"].Value.ToString().Replace("-", "").Replace("\\", "").Replace(",","").Replace(".","") + db + "_output.txt");

                        string msg = "...syncing - schemas & UDTs - database - " + db;
                        Dts.Events.FireInformation(1, "output", msg, "", 0, ref nope);

                        //string ex = @"""C:\Program Files (x86)\Red Gate\SQL Compare 13\sqlcompare.exe""";
                        string ex = Dts.Variables["$Package::RedgateFilePath"].Value.ToString();
                        ProcessStartInfo startInfo = new ProcessStartInfo();
                        startInfo.CreateNoWindow = true;
                        startInfo.UseShellExecute = false;
                        startInfo.FileName = ex;
                        startInfo.WindowStyle = ProcessWindowStyle.Hidden;
                        startInfo.Arguments = sb.ToString();

                        Process process = new Process();
                        process.StartInfo = startInfo;
                        process.Start();
                        process.WaitForExit();
                        
                    }
                    Dts.TaskResult = (int)ScriptResults.Success;
                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.ToString());
                    Dts.TaskResult = (int)ScriptResults.Failure;
                }
    }


    As you can see, after passing the arguments with "  startInfo.Arguments = sb.ToString();", the process.Start() will start to compare and sync. It does sync table and synonym very well, but it excludes "view", even if I explicitly includes view. I guess somewhere in the UI has an exclude settings? But I don't know where the include/exclude setting are from the UI. 

    Thanks  

  • Options
    Hi Todd,

    It's worth verifying if SQL Compare behaves as expected out of the SSIS code. Please open a command prompt and try:
    C:\Program Files (x86)\Red Gate\SQL Compare 13>sqlcompare /s1:SERVER1 /db1:DATABASE1 /s2:SERVER2 db2:DATABASE2 /options:DecryptPost2KEncryptedObjects,IgnoreFillfactor,IgnoreWhiteSpace,IgnoreFileGroups,IgnoreUserProperties,IgnoreWithElementOrder,IgnoreDatabaseAndServerName /include:userdefinedType /include:table /include:schema /include:view /include:identical

    Do you see views in the comparison result?

    More about the switches and options:
    https://documentation.red-gate.com/sc13/using-the-command-line/command-line-basics#Commandlinebasics-The/Optionsswitch 
    Kind regards

    Tianjiao Li | Redgate Software
    Have you visited our Help Center?
Sign In or Register to comment.