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: Do not put a space between the slash (/) and a value |
ApexSQLDataDiff /switch:”value with spaces”
|
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:
-
Text:
ApexSQLDataDiff /? /out:Help.txt
-
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:
-
Starting the application, showing application version and edition, and connecting to data source(s):
-
Comparison process, all compared tables are shown, along with the message at the end (success of failure):
-
Summary without /verbose switch:
Summary with /verbose switch:
-
Generating output(s) – synchronization script, HTML report, C# solution, executable installer, etc.:
-
Warnings without /verbose switch:
Warnings with /verbose switch:
-
The synchronization process (final execution), all objects which are being synchronized are shown, along with the message at the end (success of failure):
-
Providing a synchronization summary:
-
The name of a database that is synchronized
-
Information about creating a backup
-
The number of errors and warnings
-
The number of rows updated/added/deleted
-
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.
-
-
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.
