Getting started with Command line tools

Refers to the Professional edition only

Introduction

Command line tools (SQL Examiner CLI, SQL Data Examiner CLI) are command line versions of SQL Examiner and SQL Data Examiner. The tools allow you to run a comparison and synchronization without human intervention in different scenarios, e.g. synchronization within a single-click operation, scheduling synchronization, triggering synchronization from third-party software, etc.

If you need to distribute CLI tools as part of your application, you can do it with the special option: Redistributable command line tool containing the same CLI tools. It can be purchased separately only by users of SQL Examiner Suite (Professional edition).

The examples and descriptions below are based on SQL Data Examiner 2023. Previous versions may have minor differences.

Where command line tools can be found

The command line tools are started directly from the executable files SQLECmd.exe and SQLDECmd.exe in the program folder C:\Program Files (x86)\SQL Examiner Suite 2023.

Configuration

The command line can be configured manually, but this is beyond the scope of this article. The recommended way to configure the command line is to use CLI Wizard (Command Line Interface Wizard). After a comparison is configured, you can do some fine-tuning manually by updating the files or command line prepared in CLI Wizard.

There are three ways to pass synchronization parameters to the command line utility: Project file, Configuration file, or Command line parameters only. Each way has its advantages and disadvantages and can be used in specific scenarios.

Project file

In this case you pass to CLI previously saved project file, which defines comparison options. The actions that should be performed after comparison (e.g. synchronization) and synchronization parameters are set in the command line, and the file name is passed in the command line as a parameter:

"C:\Program Files (x86)\SQL Examiner Suite 2022\SQLDECmd.exe"  /project:"C:\Test\SynchronizeEmployee.sdeproj" /force /synchronize /options:{-SynchronizeIdentity, -FixSequenceValues, -DisableTriggers, -DisableUniques, -DisableForeignKeys, PrecedeStringConstWithNPrefix:Always, OnError:Abort, RollbackOnError:All, -SetAnsiWarningsOff, DirectDataCopy, SqlBatchMode, SqlBatchSize:1MB, AutoSelectKeyColumns:None} /include:[HumanResources].[Employee]{keycolumns:[BusinessEntityID]}

Advantages

• all features of the utility are supported
• you can configure and save parameters to project file in the GUI version of the software
• an existing project file can be opened and modified in the GUI version of the program (this also may be applied to Scheduled tasks)
• is compatible with data sources of all types

Disadvantages

• it is not possible to create and configure the file using third-party software because the format is not documented

Configuration file

All parameters are stored in a *.config file in XML format. The file name is passed as a parameter in the command line:

"C:\Program Files (x86)\SQL Examiner Suite 2022\SQLDECmd.exe"  /config:"C:\Test\SynchronizeEmployee.xml"

Advantages

• all features of the utility are supported
• you can configure and save parameters to configuration file in the GUI version of the software.

Disadvantages

• Requires creation of a file: manually or automatically

Command line parameters

All parameters are passed directly in the command prompt upon execution of the command line utility.

Advantages

• no additional files are required

Disadvantages

• less convenient for editing than a configuration file or a project file

• there are the following limitations:

  • command line length is limited: standard limit in Windows is 8191 characters (https://learn.microsoft.com/en-us/troubleshoot/windows-client/shell-experience/command-line-string-limitation)
  • doesn't support schema mapping

• limitations applicable to SQL Data Examiner only:

  • no way to define filters (WHERE clause)
  • no table/object mapping
  • comparison of queries is not supported
  • no way to determine synchronization order
  • custom data sources (e.g. Flat Files, Custom ODBC Data Source, etc.) are not supported

Overriding parameters

Command line parameters supplement what is not specified in a configuration or a project file.

Some comparison parameters specified in a file (login, password, comparison options) can be overridden by command line parameters, since they have higher priority.

Example 1: in the example below the SQL Server target database and authentication method in a configuration file are overridden by the parameters in the command line:

configuration file

<config version="2" maker="SQL Data Examiner">
  <DataSources>
    <DataSource id="1" server="desktop-ri184o3" database="AdventureWorks2019" winAuth="True" />
    <DataSource id="2" server="desktop-ri184o3" database="AdventureWorks19Test" winAuth="True" />
  </DataSources>
  ...
</config>

command line (pay attention to database2, UserID2, password2)

"C:\Program Files (x86)\SQL Examiner Suite 2022\SQLDECmd.exe"  /config:"C:\Test\SynchronizeEmployee_config.xml" /DbType1:MsSQL /server1:desktop-ri184o3 /database1:AdventureWorks2019 /WinAuth1 /DbType2:MsSQL /server2:desktop-ri184o3 /database2:TestDatabase /UserID2:sa /password2:TestPassword

In case of a project file comparison options can be either set in the GUI version of the program (for more information, see SQL Data Examiner comparison options) or overridden in the command line.

Example 2: In the example below the project file comparison options CompareTrimmedStrings, EmptyEqualsNull, IgnoreRowGUIDCol, ComparisonCaseSensitive (2 = Case Insensitive) are overridden by the corresponding options in the command line:

project file

<SqlDataExaminer version="21">
   ...
  <ProjectSettings>
    <AllSettings>
      <ComparisonCaseSensitive>2</ComparisonCaseSensitive>
      <RowMatchingMode>Performance</RowMatchingMode>
      <CompareTrimmedStrings>True</CompareTrimmedStrings>
      <EmptyEqualsNull>True</EmptyEqualsNull>
      <RoundFloatTypes>0</RoundFloatTypes>
      <AutoSelectKeyColumns>0</AutoSelectKeyColumns>
      <IgnoreRowGUIDCol>True</IgnoreRowGUIDCol>
      <PerformanceOptions>
        <PerformanceOption name="Auto" value="True" />
      </PerformanceOptions>
    </AllSettings>
  </ProjectSettings>
  ...
</SqlDataExaminer>

command line (pay attention to -CompareTrimmedStrings, -EmptyEqualsNull, -IgnoreRowGUIDCol, ComparisonCaseSensitive)

"C:\Program Files (x86)\SQL Examiner Suite 2022\SQLDECmd.exe"  /project:"C:\Test\SynchronizeEmployee_project_2.sdeproj" /force /synchronize /options:{-SynchronizeIdentity, -FixSequenceValues, -DisableTriggers, -DisableUniques, -DisableForeignKeys, PrecedeStringConstWithNPrefix:Always, OnError:Abort, RollbackOnError:All, -SetAnsiWarningsOff, DirectDataCopy, SqlBatchMode, SqlBatchSize:1MB, -CompareTrimmedStrings, -EmptyEqualsNull, -IgnoreRowGUIDCol, ComparisonCaseSensitive:CaseSensitive, AutoSelectKeyColumns:None} /include:[HumanResources].[Employee]{keycolumns:[BusinessEntityID]}