SQLCMD Commands

SQLCMD commands give the script developer substantially more flexibility beyond what would normally be supported by T-SQL language. They are part of the SQLCMD tool, and as such get executed on the client computer before the query is submitted to the server. You need to keep this in mind while using them. All the commands supported by the tool are described in this section.

Overview of SQLCMD Commands

As mentioned earlier, in addition to command line options, SQLCMD also supports many powerful commands. A SQLCMD command always starts with a new line and is preceded by a colon. A colon is required to make a clear distinction between a SQLCMD command and a T-SQL script command. However, for backward compatibility with OSQL, some legacy commands are recognized without the colon prefix.

How do you find out what commands SQLCMD supports? There is a "help" command for this, and here is the output:

1>:help :!! [<command>]   - Executes a command in the Windows command shell. :connect server[\instance] [-l timeout] [-U user [-P password]]   - Connects to a SQL Server instance. :ed   - Edits the current or last executed statement cache. :error <dest>   - Redirects error output to a file, stderr, or stdout. :exit   - Quits SQLCMD immediately. :exit()   - Execute statement cache; quit with no return value. :exit(<query>)   - Execute the specified query; returns numeric result. go [<n>]   - Executes the statement cache (n times). :help   - Shows this list of commands. :list   - Prints the content of the statement cache. :listvar   - Lists the set SQLCMD scripting variables. :on error [exit|ignore]   - Action for batch or SQLCMD command errors. :out <filename>|stderr|stdout   - Redirects query output to a file, stderr, or stdout. :perftrace <filename>|stderr|stdout   - Redirects timing output to a file, stderr, or stdout. :quit   - Quits SQLCMD immediately. :r <filename>   - Append file contents to the statement cache. :reset   - Discards the statement cache. :serverlist   - Lists local and SQL Servers on the network. :setvar {variable}   - Removes a SQLCMD scripting variable. :setvar <variable> <value>   - Sets a SQLCMD scripting variable. 1> 

It's worth noting that SQLCMD commands (unlike command line options) are not case sensitive.

Tips and Tricks

It is safe to mix SQLCMD command with T-SQL statements in the same batch because they all get pre-processed before T-SQL is submitted to the server. However, you should be aware that for any given batch, all SQLCMD commands are pre-processed and only after that is T-SQL sent to the server. It means that sometimes you can get unexpected results if you try doing too many things in the same batch. Consider, for example, the SETVAR command mentioned earlier that defines a scripting variable.

use AdventureWorks :setvar tablename Person.Address select * from $(tablename) :setvar tablename HumanResources.Department select * from $(tablename) go 

One could expect that the first select would be from Person.Address and the second one from HumanResources.Department. However, that's not the case. As already mentioned, all SQLCMD commands in a given batch are pre-processed first, and only after that is the resulting T-SQL sent to the server. In this example, both selects are from HumanResources.Department. To fix that, put the batch separator (go in the default case) between the two SETVAR commands:

:setvar tablename Person.Address select * from $(tablename) go :setvar tablename HumanResources.Department select * from $(tablename) 

Explanations and Examples of SQLCMD Commands

This section provides more information on each of the SQLCMD commands.

GO [count]

Although it does not get prefixed by colon, a batch terminator can also be considered a special command. Not only that, a batch terminator can accept a numeric value as a parameter. If supplied, the current batch executes the specified number of times, as shown in the following example:

C:\>SQLCMD 1> select 1 2> go 3 -----------------            1 (1 rows affected) -----------------            1 (1 rows affected) -----------------            1 (1 rows affected) 1> 

[:]RESET

All T-SQL statements are held in the statement cache until a batch terminator is encountered. The RESET command clears the statement cache.

Tips and Tricks

The statement cache can also be cleared by typing the ^C escape sequence at the console. However, if this sequence is used when the cache is empty, the entire SQLCMD execution session is terminated.

[:]ED

This command enables the user to call an editor on the current query buffer. The editor of choice is defined by the SQLCMDEDITOR environment variable. The default editor is "edit," provided by the operating system. After the editor exits, the entire batch is entered directly into the statement cache.

Tips and Tricks

The ED command also enables you to edit the last executed batch if the current statement cache is empty. This is especially useful when you realized you made a mistake and the previous batch returned syntax errors. Quick editing and submitting can save time.

[:]!! <command>

Typing !! allows the user to pass essentially any operating system command straight to the command line interpreter for direct execution and passes the output back to your display.

Warning

This is a very powerful option that can allow a malicious script writer to break through your security. If you are not sure of the contents of the script, you should consider using the -X command line option while executing it.

Support of the !! command and its implementation makes interesting scenarios possible while inside Query Editor that have never been possible before. For example, you can see a list of all files in your Program Files folder on your local machine inside the Messages tab if you do the following while in SQLCMD mode:

!!dir "%programfiles%" 

Note that there is no dir.exe command, but it is available while inside the cmd.exe command interpretor, so the command works successfully; it gets expanded to something like this (depending on location of your Windows directory).

"C:\WINDOWS\system32\cmd.exe /C dir" 

In general, any command is passed as a parameter to the Windows cmd.exe shell.

Tips and Tricks

As with other SQLCMD commands, !! supports specifying a SQLCMD variable as its parameter.

Consider an example of backing up the Pubs database to a share. Remember that the database is on the server to which your query window is connected, but the command specified to the !! command is executed on your local computer on which you run SQL Server Management Studio. For the purpose of this example, imagine that your file server name is "myserver" and the share name that you (and the account under which SQL Server service runs) can access is called "backups."

:setvar backupDir "\\myserver\backups\pubs" !!if not exist $(backupDir) md $(backupDir) GO    BACKUP DATABASE [pubs] TO    DISK = N'$(backupDir)\pubs.bak' WITH NOFORMAT, NOINIT,    NAME = N'pubs-Full Database Backup', SKIP, NOREWIND,    NOUNLOAD,  STATS = 10 GO 

In this query, SQLCMD variables are used to share context between the !! command and the SQL Query. The first line introduces a new SQLCMD variable called backDir and initializes it with the file share. The second line uses the command interpretor's language to ensure that the directory exists. After that, the directory name is passed in to the T-SQL backup database statement to back up the database to the specified share.

Tips and Tricks

If you try executing this example and you receive an Access Denied message output to your Messages tab, it probably means that either you or the user under which SQL Server runs do not have write permission for the share.

This example uses command shell language directly inside a query window. Usually it is preferable to encapsulate it into a batch file and then call the file from the !! command. Let's rework this query to use the batch file to create a directory for the BAK file.

Open your favorite text editor and create a c:\batches\prepare.bat that contains the following statement:

if not exist "%1" md "%1" 

This means that it checks to see whether the directory specified as the first parameter to the batch file exists, and if it does not, then it creates it.

After that, adjust the query like this:

:setvar batchFile c:\batches\prepare.bat :setvar backupDir "\\myserver\backups\pubs" !!$(batchFile) $(backupDir) GO    BACKUP DATABASE [pubs] TO    DISK = N'$(backupDir)\pubs.bak' WITH NOFORMAT, NOINIT,    NAME = N'pubs-Full Database Backup', SKIP,    NOREWIND, NOUNLOAD,  STATS = 10 GO 

It does exactly the same thing as the previous query, only this time it calls the batch file and specifies the backupDir SQLCMD variable as the parameter.

[:]QUIT

This command immediately ends the current SQLCMD session without running any statements in the statement cache.

[:]EXIT[(statement)]

This function has three distinctly different formats.

• :EXIT supplied without parenthesis has the same affect as QUIT.

• :EXIT() first executes previously accumulated statements and only then exits the session.

• :EXIT(query) first executes previously accumulated statements, then the supplied query. Finally it quits after returning results of the query as a return value from the SQLCMD tool. If the query is a SELECT statement returning multiple result sets, the first column of the first row of the last result set is converted to a fourbyte integer value and is used as the return code from the program.

Tips and Tricks

In general, SQLCMD propagates an error message number as the return code to the calling program.

Suppose a script causes the following RAISERROR statement to be executed: RAISERROR(30301, 16, 130). The error causes SQLCMD to end and the message ID 30301 to be returned to the client.

In case SQLCMD is not able to process the supplied query and return a value to the client, it can return one of the following reserved error codes for which a calling program can test:

• -100 means an error occurred prior to selecting the return value

• -101 means no rows were returned by the query

• -102 means a conversion error occurred when selecting the return value

:SETVAR <variable name> ["<variable value>"]

The SETVAR option, already mentioned earlier during the discussion on scripting variables, enables you to set their values programmatically inside the script.

One interesting aspect of the SETVAR command is that it is possible to assign a value that consists of multiple words. In this case, the variable value should be enclosed into the double quotes characters.

:setvar querytext "select * from pubs..authors" $(querytext) 

The query above results in "select * from pubs..authors" being submitted to the server.

It is possible to define a variable that spans multiple lines, as long as it is enclosed in quotes and can be executed as one batch.

Try executing

:setvar querytext "select au_lname from pubs..authors where au_id = '172-32-1176' " $(querytext) 

You'll see the following result:

au_lname ------------------------------------ White (1 row(s) affected) 

In order to undefine a previously defined variable, use the SETVAR syntax without specifying the variable value:

:setvar <variable name> 

It undefines the previously defined variable. For example,

:setvar ProcName sp_who exec $(ProcName) go :setvar ProcName exec $(ProcName) go 

results in the following error during execution of the second exec statement: "A fatal scripting error occurred. Variable ProcName is not defined."

:r <filename>

This option enables the script writer or interactive user to read the contents of a specified script file and include it in the current execution session. The included file is processed in-place; in other words, every command encountered is processed the same way, as if it was just typed in or was part of the original file.

The read command enables you to create complex compound scripts to potentially leverage simpler generic scripts and drive their execution through different scripting variable settings.

Let's use a previously created backup.sql script containing the following entry

BACKUP DATABASE $(db) TO DISK = '$(path)\$(db).bak' 

Only this time it is included inside the compound script and backs up two databases at once, as follows.

:setvar db pubs :setvar path C:\data :r "c:\sqlscripts\backup.sql" GO :setvar db northwind :setvar path C:\data:r "c:\sqlscripts\backup.sql" GO 

Tips and Tricks

The extension of the file specified to the :r command does not matter. You could have TXT or any other extension; SQLCMD simply treats the file as a text file containing T-SQL statements.

A very helpful feature of the :r command is that you can specify a SQLCMD variable as the parameter. That enables you, among other things, to quickly try different queries depending on a variable value or to logically include setting a file name into the variable definition part of your queries. Consider the following example:

:setvar filename "c:\sqlscripts\sp_who.sql" :r $(filename) go :setvar filename "c:\sqlscripts\sp_lock.sql" :r $(filename) This is equivalent to the following: :r c:\sqlscripts\sp_who.sql go :r c:\sqlscripts\sp_lock.sql 

By itself, this doesn't look like a useful feature, but consider what happens when you have multiple :r commands that read different files. In this case, you can have SQLCMD variables with filenames defined at the top of your query file and use them as needed later:

:setvar firstQuery "c:\sqlscripts\sp_who.sql" :setvar secondQuery "c:\sqlscripts\sp_lock.sql" :setvar thirdQuery "<some file>" :setvar finalQuery "<some file>" <100 lines of SQL statements> :r $(secondQuery) GO <200 lines of SQL statements> :r $(firstQuery) GO <more SQL statements> ... . etc 

SQLCMD enables you to include nested :r statements, causing one file to include another one and so on. Be careful, however, with the level of nested files that you load with the :r command. If you end up with too many levels on indirection (you load a file that loads a file that loads a file, etc.), it might be difficult to debug the resulting script and to maintain it. After all :r commands have been processed, the query editor doesn't have information about which file contained which executed batch, so if there are errors during execution, it might be a challenge to correlate them with the correct query file. In such situations it's a good idea to use print statements to trace the execution order. For example, you might want to adjust your stored_proc.sql statement like this:

:setvar filename "c:\sqlscripts\sp_who.sql" print '$(filename)' :r $(filename) go :setvar filename "c:\sqlscripts\sp_lock.sql" print '$(filename)' :r $(filename) 

To summarize, the :r command enables the user to easily group and parameterize your SQL queries so you can have a set of SQL files doing different logical operations that can later be combined into a single query. With this command, you don't have to maintain one huge SQL query that does hundreds of different things. You can keep different operations in their own files and combine and parameterize them as needed by using the :r command and SQLCMD variables, as demonstrated by the preceding short examples.

:SERVERLIST

This command corresponds to the command line option -L, listing all locally configured servers and the names of the servers broadcasting on the network. How does it help you to know the names of all servers when you are already connected? SQLCMD enables a user to reconnect to a different server within a session. Here is how:

:CONNECT <SQL Server Database Engine instance name> [-l timeout] [-U user [-P password]] 

As soon as SQLCMD connects to a new server, the current connection is closed. At the same time the SQLCMDSERVER variable is updated accordingly.

An optional login timeout switch allows infinite timeout (0), or some finite number of seconds. If not specified, the default comes from the SQLCMDLOGINTIMEOUT variable.

The default authentication mode is Windows integrated security. However, if the environment variable SQLCMDUSER is set or option -U specified, SQLCMD attempts to use it to establish a connection instead. In lieu of a password value provided via SQLCMDPASSWORD or the -P flag, SQLCMD interactively asks the user to enter a password on the command line.

:LISTVAR and :LIST

The LISTVAR command was mentioned earlier during the introduction of scripting variables. It lists currently defined variables and, as such, is handy in troubleshooting SQLCMD.

There is a different command, LIST, which enables you to list contents of the current statement cache.

:ERROR <filename>/STDERR/STDOUT

This command enables the user to redirect error output, either to a specified file or to standard error/standard output console streams. You may want to refer back to command line option -r, discussed earlier in this chapter.

Tips and Tricks

Error redirection instructions can appear multiple times in the same session or script, each time changing where errors are going to be reported.

Similar redirection options exist for output and performance trace data.

:OUT <filename>/STDERR/STDOUT

This command redirects all query results output that by default gets sent to standard output.

:PERFTRACE <filename>/STDERR/STDOUT

The :PERFTRACE command redirects all performance information (see the discussion of the -p option) that by default goes to standard output.

:ON ERROR [exit/ignore]

This command instructs SQLCMD on how to handle errors during script execution. The following options are available:

• exit Exits the program with the appropriate error value

• ignore Ignores the error and continues execution. An error message is printed.

:XML [ON/OFF]

When you expect XML data to be coming back from the server, for example because of a FOR XML clause in your query, you should set XML mode to ON via this command, to properly format the output. By default it is set to OFF and thus displays unformatted data in a continuous stream.

One side effect of setting the XML mode to ON is that error messages also appear in XML format. This option does not affect formatting of any other non-XML columns present in the returned data set.

Tips and Tricks

Setting XML mode ON and OFF needs to be done only upon the start of a new batch; otherwise, results are indeterminate. Consequently, XML and regular data results cannot be mixed in the same batch or data of one of these two types becomes unreadable.