SQLCMD Quick Reference
SQLCMD is the standard text-based tool for executing batches of T-SQL on SQL Server. As a text-based tool, SQLCMD provides a lightweight but powerful tool for automating T-SQL batches. This appendix is designed as a quick reference to SQLCMD. The descriptions of many of the features and the functionality given here differ from BOL in some instances; the descriptions provided in this appendix are based on extensive testing of SQLCMD.
Command-Line Options
SQLCMD provides several command-line options to provide flexibility in connecting to SQL Server and executing T-SQL batches in a database. The full format for SQLCMD is shown here:
sqlcmd [ [-U login_id ] [-P password ] | [-E] ] [-C]
[-S server [instance] ] [-d db_name] [-H workstation]
[-l login timeout] [-t query timeout] [-h headers] [-s column_separator] [-w column_width]
[-a packet_size] [-I] [-L[c] ] [-W] [-r[o|1]] [-q "query"] [-Q "query" and exit]
[-c batch_term] [-e] [-m error Level] [-V Severity Level] [-b] [-N] [-K]
[-i input_file [,input_file2 [, ...] ] ] [-o output_file] [-u]
[-v var = "value" [,var2 = "value2"] [,...] ] [-X[1 ] [-x] [-?]
[-z new_password] [-Z new_password] [-f codepage | i:in_codepage [,o:out_codepage] ]
[-k[l|2] ] [-y display_width] [-Y display_width]
[-p[1 ] [-R] [-A]
The available command-line options are listed in Table D-1. The SQLCMD command-line options are case sensitive, so, for example, -v is a different option from -V.
Table D-1. SQLCMD Command-Line Options
Scripting Variables
SQLCMD supports scripting variables, which allow you to dynamically replace script content at execution time. This lets you use a single script in multiple scenarios. By using scripting variables, for instance, you can execute a single script against different servers or databases without modification. SQLCMD allows you to set your own custom scripting variables with the -v command-line option. If more than one scripting variable is specified with the same name, the variable with the highest precedence (according to the following list) is used:
- System-level environment variables have the highest precedence.
- User-level environment variables are next.
- Variables set via the command shell SET option are next.
- Variables set via the SQLCMD -v command-line option are next.
- Variables set inside a SQLCMD batch via the :SETVAR command have the lowest precedence.
Note The -X and -x options disable startup-script execution and environment-variable access, respectively. -x also prevents SQLCMD from dynamically replacing scripting-variable references in your code with the appropriate values. This is a feature designed for secure environments where scripting-variable usage could compromise security.
SQLCMD also provides several predefined scripting variables, which are listed in Table D-2. You can set the predefined read-only SQLCMD scripting variables via the command shell SET option or through SQLCMD command-line options; you can’t alter them from within a SQLCMD script with :SETVAR.
Table D-2. SQLCMD Scripting Variables
SQLCMD recognizes a set of commands that aren’t part of T-SQL. These SQLCMD commands aren’t recognized by other query tools; they’re not even recognized by SSMS (except when you run it in SQLCMD mode). SQLCMD commands all begin on a line with a colon (:) to identify them as different from T-SQL statements. You can intersperse SQLCMD commands within your T-SQL scripts. Table D-3 lists the SQLCMD commands available.
Tip For backward compatibility with older osql scripts, you can enter the following commands without a colon prefix: !!, ED, RESET, EXIT, and QUIT. Also, SQLCMD commands are case insensitive, they must appear at the beginning of a line, and they must be on their own line. A SQLCMD command can’t be followed on the same line by a T-SQL statement or another SQLCMD command.
Table D-3. SQLCMD Commands
|
Command |
Description |
|---|---|
|
:!! |
Invokes the command shell. This command executes the specified operating system command in the command shell. |
|
:CONNECT server [instance] |
Connects to a SQL Server instance. |
|
[-ltimeout] [-Uuser [-Ppassword] ] |
The server name (server) and instance name (instance) are specified in the command. When :CONNECT is executed, the current connection is closed. You can use the following options with the :CONNECT command: -l specifies the login timeout (specified in seconds; 0 equals no timeout); -U specifies the SQL authentication username; and -P specifies the SQL authentication password. |
|
:ED |
Starts the text editor to edit the current batch or the last executed batch. The SQLCMDEDITOR environment variable defines the application used as the SQLCMD editor. The default is the Windows EDIT utility. |
|
:ERROR destination |
Redirects error messages to the specified destination. destination can be a file name, STDOUT for standard output, or STDERR for standard error output. |
|
:EXIT [()|(query)] |
Has three forms: :EXIT alone immediately exits without executing the batch and with no return code. :EXIT() executes the current batch and exits with no return code. :EXIT(query) executes the batch, including the query specified, and returns the first value of the first result row of the query as a 4-byte integer to the operating system. |
|
GO [n] |
The batch terminator. It executes the statements in the cache. If n is specified, GO executes the statement n times. |
|
:HELP |
Displays a list of SQLCMD commands. |
|
:LIST |
Lists the contents of the current batch of statements in the statement cache. |
|
:LISTVAR |
Lists all the SQLCMD scripting variables (that have been set) and their current values. |
|
:ON ERROR action |
Specifies the action SQLCMD should take when an error is encountered. action can be one of two values: EXIT stops processing and exits, returning the appropriate error code. IGNORE disregards the error and continues processing. |
|
:OUT destination |
Redirects output to the specified destination. destination can be a file name, STDOUT for standard output, or STDERR for standard error output. Output is sent to STDOUT by default. |
|
:PERFTRACE destination |
Redirects performance trace/timing information to the specified destination. destination can be a file name, STDOUT for standard output, or STDERR for standard error output. Trace information is sent to STDOUT by default. |
|
:QUIT |
Quits SQLCMD immediately. |
|
:R filename |
Reads in the contents of the specified file and appends it to the statement cache. |
|
:RESET |
Resets/clears the statement cache. |
|
:SERVERLIST |
Lists all SQL Server instances on the local machine and any servers broadcasting on the local network. If SQLCMD doesn’t receive timely responses from a server on the network, it may not be listed. |
|
:SETVAR var [value] |
Allows you to set or remove SQLCMD scripting variables. To remove a SQLCMD scripting variable, use the :SETVAR var format. To set a SQLCMD scripting variable to a value, use the :SETVAR var value format. |
|
:XML ON|OFF |
Indicates to SQLCMD that you expect XML output from SQL Server (that is, the SELECT statement’s FOR XML clause). Use :XML ON before your SQL batch is run and :XML OFF after the batch has executed (after the GO batch terminator). |