General usage and the common Command Line Interface (CLI) switches for ApexSQL tools

Applies to
All ApexSQL console applications with Command line interfaces (CLI)

Summary
This article describes general usage of the Command Line Interface (CLI) and the common CLI switches for all ApexSQL tools with CLI support

Description

General usage

In order to access the CLI of ApexSQL tools with all privileges and to execute any command, the Command Prompt has to be run as administrator.

To start the CLI for ApexSQL tools navigate to the application’s installation folder by using the “cd” console command and enter ApexSQLProductName.com in the console. In the following example, ApexSQL Data Diff is used:

Also, it is possible to call the console application with its explicit ApexSQLProductName, without com. at the end

For example: ApexSQLDataDiff

Format of switches

  • Switches are case insensitive. For example: /S: is the same as /s:
  • Switches are preceded with one forward slash (/). For example: /db
  • If a switch has additional arguments, it is followed by a colon (:) before an argument value is specified. For example: /db:pubs
  • All switches have abbreviated (alias) name (with 1-3 chars) besides the full name. For example: for the /project switch abbreviated (alias) is /pr
  • Switches are delimited with a white space from each other
  • Switches can be put in arbitrary order. For example:/s: /db: will produce the same results as /db:/s:
  • Multi-argument switches support abbreviated arguments. For example: for the/excel_report_options:Identical Different Checked abbreviated arguments are /excel_report_options:i d c
  • Default values for omitted parameters are used in most cases. Default values are specified in the /help switch under the following format: (“<default>” if omitted) or Default – combination of specific options
  • Values passed explicitly to switches override values contained in projects files, profiles, etc.
  • /exclude switch overrides /include one when both are specified

Passing values

ApexSQL Data Diff is used in the following examples. If another application is used, simply use that product name.

Values are passed to switches in the following format:

ApexSQLDataDiff /switch:value

Quick tip icon

Quick tip:

Do not put a space between the slash (/) and a value

ApexSQLDataDiff /switch:”value with spaces”

Quick tip icon

Quick tip:

Use double quotes for values that contain spaces in order to avoid bad format of a specified switch

CLI help

  • Group of switches are properly labeled with a specific title and followed by (:).
    For example:

    Additional options:
    /? [ /help ] Shows CLI help

  • Default values for omitted parameters should be used. Default values should be specified in the following format (“<default>“ if omitted), e.g.:

    /s:arg [ /server ] Server name ((local) if omitted)

  • Switch help string is shown in the following format:

    /u1:arg [ /user1 ]
    Source login when SQL Server authentication is used (trusted connection is used if the switch is omitted)

    /f [ /force ]
    Allows overwriting of existing files

  • CLI help can be outputted in two different formats:

    1. Text:

      ApexSQLDataDiff /? /out:Help.txt

    2. HTML:

      ApexSQLDataDiff /hth:Help.txt

SQL Server connection handling

  • If a server is not specified “(local)” is assumed

  • If a user is not specified trusted connection is assumed

  • When connecting to the server, the following message will be displayed:

    Connecting to: <server>.<database> using <authentication> authentication [with username]

Common return error codes

All ApexSQL console supported applications have a built-in support for the common return error codes. The list below contains the currently supported common return error codes:

CodeMessageCLI output
0SuccessThe <operation> has finished successfully
1General error codeAn error has occurred: <error>
2Invalid switchSwitch “<switch>” does not exist

Type ApexSQL<application name>.com /? for help

3Duplicated switchSwitches <switch1>, <switch2>… cannot be used more than once

Type ApexSQL<application name>.com /? for help

4Incompatible switchesSwitches <switch1> and <switch2> cannot be used together

Type ApexSQL<application name>.com /? for help

5Missing argumentMissing required argument(s) for switch <switch>

Type ApexSQL<application name>.com /? for help

6Missing a dependent switchSwitch <switch1> is missing its dependent switch <switch2>

Type ApexSQL<application name>.com /? for help

7Invalid valueThe value <value> for switch <switch> is invalid
8Value out of rangeThe numeric value provided for switch <switch> is out of the range. Allowed values are <range start> – <range end>
9Invalid file pathSpecified file path: <path> is invalid or the file does not exist
10Invalid project fileSpecified project file: <path to project file> is not a valid project file for ApexSQL <application name>
11Unsupported editionThe Command Line Interface is not supported in the <edition> edition. To purchase the Professional edition, please contact sales@apexsql.com
12Invalid activation keyThe entered activation key is invalid. Please check that the correct activation key is entered or contact sales@apexsql.com for help
13Trial has expiredTrial has expired. To extend your trial or purchase the Professional edition, please contact sales@apexsql.com
14Connection timeoutConnection to server <server> has timed out. Make sure that SQL Server is accessible and try again
15Connection failedConnection to server <server> has failed: <error description>
16IO errorThe application attempted to write to a file that already exists and the /force switch has not been set
17Operation canceledThe operation was canceled because the Ctrl+C or the Ctrl+Break was used
18Application errorThe following error has occurred: <error>
19Execution errorThe execution of <script> has failed with following error: <error>
20Execution timeoutThe execution of <script> has timed out with the following error: <error>
21Insufficient permissionsThe operation cannot be completed because the user does not have the required permissions

To obtain a return code, use the following example with ApexSQL Data Diff:

ApexSQLDataDiff /? /out:help.txt

@echo ApexSQL Data Diff return error code is %errorlevel%

ApexSQL Data Diff return error code is 0

Since the return error code is 0, it means that the operation (outputting the CLI help as a txt file) was successful.

Specific return error codes

Specific error codes are custom for each tool and their values are > 100.

For example:

103 – Output file creating failed

An issue was encountered while creating the output file and caused the failure

Warnings

If warnings are applicable in the ApexSQL tool, warnings will be shown before the “final” execution in CLI, e.g. Warnings are shown before executing the synchronization script in ApexSQL Data Diff.

The following warnings handling switch are available in ApexSQL tools, where it’s applicable:

SwitchAlias:Description
/warnings_handling/whSpecifies warnings handling before the application execution:

IgnoreWarnings [is] – warnings will be ignored during the application execution

AbortOnMediumOrHighWarnings [amh] – application execution will be aborted on medium or high warnings

AbortOnHighWarnings [ah] – application execution will be aborted only on high warnings

Default [dft] – sets the IgnoreWarnings option

Arguments cannot be combined.

Special and shared CLI switches

Switches from the list below can be found in the Connection and Additional options groups in the CLI help:

SwitchAlias:Description
/argfile/af:argSpecifies an argument file with CLI options.

This should be an XML file that has tags equivalent to “switch name” and “switch value”.

Example:

<argfile>

<s>MyServer\SQL2016</s>

<d>MyDatabase</d>

<f/>

<v/>

</argfile>

/project/pr:argFull path to the project file, along with <.axnn> extension
/server/s:argServer name ((local) if omitted)
/database/d:argDatabase name
/user/u:argDatabase login (trusted connection if omitted)
/password/p:argPassword specified along with /user switch
/test/tOutputs parsed command line switches without taking any actions
/verbose/vPrints messages that are suppressed by default
/force/fAllows overwriting of existing files
/silent/sltDisables all output to console
/out_to/outRedirects console output to specified file
/help/?Shows CLI help
/help_to_html/hth:argFull path to the CLI help file, along with <.html> extension
/return_codes/rcOutputs all return codes to console
/beep/bePlays sound upon finished operation

Messages and summaries

Where applicable, summaries of application execution phases are shown in the CLI output. The /verbose

switch controls the amount of information that will be displayed.

In the following example, the whole CLI process is shown in ApexSQL Data Diff:

  1. Starting the application, showing application version and edition, and connecting to data source(s):

  2. Comparison process, all compared tables are shown, along with the message at the end (success of failure):

  3. Summary without /verbose switch:

    Summary with /verbose switch:

  4. Generating output(s) – synchronization script, HTML report, C# solution, executable installer, etc.:

  5. Warnings without /verbose switch:

    Warnings with /verbose switch:

  6. The synchronization process (final execution), all objects which are being synchronized are shown, along with the message at the end (success of failure):

  7. Providing a synchronization summary:

    1. The name of a database that is synchronized

    2. Information about creating a backup

    3. The number of errors and warnings

    4. The number of rows updated/added/deleted

    5. Total number of rows which are synchronized

    See /warnings.log contains simple/detailed warnings depending on usage of /verbose

    See /changes.log contains detailed changes as explained below.

  8. Changes summary is shown when the /verbose switch is used, at the end of the application execution:

Q: Are the connection switches the same for all ApexSQL tools?

A: Yes, except for the tools that have source and destination, such as ApexSQL Data Diff and ApexSQL Diff. In this case, numbers (1 – source and 2 – destination) are added to the connection switches.

For example, if you have two databases on two different servers which you want to compare with ApexSQL Data Diff, the command line will look like this:

ApexSQLDataDiff /s1:SourceServer /d1:SourceDB /s2:DestinationServer /d2:DestinationDB

Q: Is there any particular order in which CLI switches have to be entered?

A: No. CLI switches can be entered in any order.

Q: Can I override options that are saved in the project file?

A: Yes. If you add a CLI switch, which is already specified with an option in a project file, a CLI switch will override the specified option in a project file.