How the SQL compare include/exclude object types works

• 
  Can you share the full command line script?
  
  Thanks.

  Tianjiao_Li August 22, 2019 16:02

  - 0 +
• 
  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

  ToddY August 22, 2019 17:51

  - 0 +
• 
  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?
  

  Tianjiao_Li August 23, 2019 12:57

  - 0 +
• 
  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 August 23, 2019 13:13

  - 0 +
• 
  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  

  
  

  ToddY August 23, 2019 13:19

  - 0 +
• 
  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 
  

  Tianjiao_Li August 27, 2019 12:10

  - 0 +

Add comment

Please sign in to leave a comment.