How the SQL compare include/exclude object types works

ToddY

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?

Tianjiao_Li

Can you share the full command line script?

Thanks.

ToddY

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

Tianjiao_Li

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?

ToddY

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,

ToddY

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(@ /Exclude:user /Exclude:role /Exclude:ddltrigger /Exclude:partitionscheme /Exclude:partitionfunction);

                    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  

Tianjiao_Li

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