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:

Code Message CLI output
0 Success The <operation> has finished successfully
1 General error code An error has occurred: <error>
2 Invalid switch Switch “<switch>” does not exist

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

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

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

4 Incompatible switches Switches <switch1> and <switch2> cannot be used together

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

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

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

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

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

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

Switch Alias: Description
/warnings_handling /wh Specifies 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:

Switch Alias: Description
/argfile /af:arg Specifies 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:arg Full path to the project file, along with <.axnn> extension
/server /s:arg Server name ((local) if omitted)
/database /d:arg Database name
/user /u:arg Database login (trusted connection if omitted)
/password /p:arg Password specified along with /user switch
/test /t Outputs parsed command line switches without taking any actions
/verbose /v Prints messages that are suppressed by default
/force /f Allows overwriting of existing files
/silent /slt Disables all output to console
/out_to /out Redirects console output to specified file
/help /? Shows CLI help
/help_to_html /hth:arg Full path to the CLI help file, along with <.html> extension
/return_codes /rc Outputs all return codes to console
/beep /be Plays 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.