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.
Scripting Variables
SQLCMD supports scripting variables, which allow you to dynamically replace script content at execution time. This allows you to 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 cannot alter them from within a SQLCMD script with :SETVAR.
Table D-2. SQLCMD Scripting Variables
SQLCMD recognizes a set of commands that are not part of T-SQL. These SQLCMD commands are not recognized by other query tools; they’re not even recognized by SSMS (except when running 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 cannot be followed on the same line by a T-SQL statement or another SQLCMD command.
Table D-3. SQLCMD Commands
| Command | Description |
|---|---|
| :!! command | The :!! command invokes the command shell. It executes the specified operating system command in the command shell. |
| :CONNECT server [instance] | The :CONNECT command 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: the -l option specifies the login timeout (specified in seconds; 0 equals no timeout); the -U option specifies the SQL authentication username; the -P option specifies the SQL authentication password. |
| :ED | The :ED command 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 | The :ERROR command 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)] | The :EXIT command 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] | GO is the batch terminator. The GO batch terminator executes the statements in the cache. If n is specified, GO will execute the statement n times. |
| :HELP | The :HELP command displays a list of SQLCMD commands. |
| :LIST | The :LIST list command lists the contents of the current batch of statements in the statement cache. |
| :LISTVAR | The :LISTVAR command lists all the SQLCMD scripting variables (that have been set) and their current values. |
| :ON ERROR action | The :ON ERROR command 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 | The :OUT command 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 | The :PERFTRACE command 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 | The :QUIT command quits SQLCMD immediately. |
| :R filename | The :R command reads in the contents of the specified file and appends it to the statement cache. |
| :RESET | The :RESET command resets/clears the statement cache. |
| :SERVERLIST | The :SERVERLIST command 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] | The :SETVAR command 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 | The :XML command indicates to SQLCMD that you expect XML output from SQL Server (i.e., 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). |