In addition to Transact-SQL statements within sqlcmd, the following commands are also available:
|
GO [count]
|
:List
|
|
[:] RESET
|
:Error
|
|
[:] ED
|
:Out
|
|
[:] !!
|
:Perftrace
|
|
[:] QUIT
|
:Connect
|
|
[:] EXIT
|
:On Error
|
|
:r
|
:Help
|
|
:ServerList
|
:XML [ON | OFF]
|
|
:Setvar
|
:Listvar
|
Be aware of the following when you use sqlcmd commands:
-
All sqlcmd commands, except GO, must be prefixed by a colon (:).
.gif) Important: |
|---|
|
To maintain backward compatibility with existing osql scripts, some of the commands will be recognized without the colon. This is indicated by the [:].
|
-
sqlcmd commands are recognized only if they appear at the start of a line.
-
All sqlcmd commands are case insensitive.
-
Each command must be on a separate line. A command cannot be followed by a Transact-SQL statement or another command.
-
Commands are executed immediately. They are not put in the execution buffer as Transact-SQL statements are.
-
Editing Commands
-
- [:] ED
-
Starts the text editor. This editor can be used to edit the current Transact-SQL batch, or the last executed batch. To edit the last executed batch, the ED command must be typed immediately after the last batch has completed execution.
The text editor is defined by the SQLCMDEDITOR environment variable. The default editor is 'Edit'. To change the editor, set the SQLCMDEDITOR environment variable. For example, to set the editor to Microsoft Notepad, at the command prompt, type:
SET SQLCMDEDITOR=notepad
- [:] RESET
-
Clears the statement cache.
- :List
-
Prints the content of the statement cache.
-
Variables
-
-
:Setvar <var> [ "value" ]
-
Defines sqlcmd scripting variables. Scripting variables have the following format: $(VARNAME).
Variable names are case insensitive.
Scripting variables can be set in the following ways:
-
Implicitly using a command-line option. For example, the -l option sets the SQLCMDLOGINTIMEOUT sqlcmd variable.
-
Explicitly by using the :Setvar command.
-
By defining an environment variable before you run sqlcmd.
| Note: |
|---|
|
The -X option prevents environment variables from being passed on to sqlcmd.
|
If a variable defined by using :Setvar and an environment variable have the same name, the variable defined by using :Setvar takes precedence.
Variable names must not contain blank space characters.
Variable names cannot have the same form as a variable expression, such as $(var).
If the string value of the scripting variable contains blank spaces, enclose the value in quotation marks. If a value for a scripting variable is not specified, the scripting variable is dropped.
- :Listvar
-
Displays a list of the scripting variables that are currently set.
| Note: |
|---|
|
Only scripting variables that are set by sqlcmd, and those that are set using the :Setvar command will be displayed.
|
-
Output Commands
-
-
:Error
<
filename
>|
STDERR|STDOUT
-
Redirect all error output to the file specified by file name, to stderr or to stdout. The Error command can appear multiple times in a script. By default, error output is sent to stderr.
-
file name
-
Creates and opens a file that will receive the output. If the file already exists, it will be truncated to zero bytes. If the file is not available because of permissions or other reasons, the output will not be switched and will be sent to the last specified or default destination.
-
STDERR
-
Switches error output to the stderr stream. If this has been redirected, the target to which the stream has been redirected will receive the error output.
-
STDOUT
-
Switches error output to the stdout stream. If this has been redirected, the target to which the stream has been redirected will receive the error output.
-
:Out < filename>| STDERR| STDOUT
-
Creates and redirects all query results to the file specified by file name, to stderr or to stdout. By default, output is sent to stdout. If the file already exists, it will be truncated to zero bytes. The Out command can appear multiple times in a script.
-
:Perftrace < filename>| STDERR| STDOUT
-
Creates and redirects all performance trace information to the file specified by file name, to stderr or to stdout. By default performance trace output is sent to stdout. If the file already exists, it will be truncated to zero bytes. The Perftrace command can appear multiple times in a script.
-
Execution Control Commands
-
-
:On Error[ exit | ignore]
-
Sets the action to be performed when an error occurs during script or batch execution.
When the exit option is used, sqlcmd exits with the appropriate error value.
When the ignore option is used, sqlcmd ignores the error and continues executing the batch or script. By default, an error message will be printed.
- [:] QUIT
-
Causes sqlcmd to exit.
- [:] EXIT[ (statement) ]
-
Lets you use the result of a SELECT statement as the return value from sqlcmd. The first column of the first result row is converted to a 4-byte integer (long). MS-DOS passes the low byte to the parent process or operating system error level. Windows 200x passes the whole 4-byte integer. The syntax is:
:EXIT(query)
For example:
:EXIT(SELECT @@ROWCOUNT)
You can also include the EXIT parameter as part of a batch file. For example, at the command prompt, type:
sqlcmd -Q "EXIT(SELECT COUNT(*) FROM '%1')"
The sqlcmd utility sends everything between the parentheses ( ) to the server. If a system stored procedure selects a set and returns a value, only the selection is returned. The EXIT( ) statement with nothing between the parentheses executes everything before it in the batch and then exits without a return value.
When an incorrect query is specified, sqlcmd will exit without a return value.
Here is a list of EXIT formats:
Does not execute the batch, and then quits immediately and returns no value.
Executes the batch, and then quits and returns no value.
Executes the batch that includes the query, and then quits after it returns the results of the query.
If RAISERROR is used within a sqlcmd script and a state of 127 is raised, sqlcmd will quit and return the message ID back to the client. For example:
RAISERROR(50001, 10, 127)
This error will cause the sqlcmd script to end and return the message ID 50001 to the client.
The return values -1 to -99 are reserved by SQL Server; sqlcmd defines the following additional return values:
|
Return Values
|
Description
|
|---|
|
-100
|
Error encountered prior to selecting return value.
|
|
-101
|
No rows found when selecting return value.
|
|
-102
|
Conversion error occurred when selecting return value.
|
-
GO [count]
-
GO signals both the end of a batch and the execution of any cached Transact-SQL statements. When specifying a value for count, the cached statements will be executed count times, as a single batch.
-
Miscellaneous Commands
-
-
:r < filename>
-
Parses additional Transact-SQL statements and sqlcmd commands from the file specified by <filename> into the statement cache.
If the file contains Transact-SQL statements that arenot followed by GO, you must enter GO on the line that follows :r.
| Note: |
|---|
|
<filename> is read relative to the startup directory in which sqlcmd was run.
|
The file will be read and executed after a batch terminator is encountered. You can issue multiple :r commands. The file may include any sqlcmd command. This includes the batch terminator GO.
| Note: |
|---|
|
The line count that is displayed in interactive mode will be increased by one for every :r command encountered. The :r command will appear in the output of the list command.
|
-
:Serverlist
-
Lists the locally configured servers and the names of the servers broadcasting on the network.
-
:Connect
server_name[\instance_name] [-l timeout] [-U user_name [-P password]]
-
Connects to an instance of SQL Server. Also closes the current connection.
Time-out options:
|
0
|
wait forever
|
|
n>0
|
wait for n seconds
|
The SQLCMDSERVER scripting variable will reflect the current active connection.
If timeout is not specified, the value of the SQLCMDLOGINTIMEOUT variable is the default.
If only user_name is specified (either as an option, or as an environment variable), the user will be prompted to enter a password. This is not true if the SQLCMDUSER or SQLCMDPASSWORD environment variables have been set. If neither options nor environment variables are provided, Windows Authentication mode is used to login. For example to connect to an instance, instance1, of SQL Server, myserver, by using integrated security you would use the following:
:connect myserver\instance1
To connect to the default instance of myserver using scripting variables, you would use the following:
:setvar myusername test
:setvar myservername myserver
:connect $(myservername) $(myusername)
- [:] !!< command>
-
Executes operating system commands. To execute an operating system command, start a line with two exclamation marks (!!) followed by the operating system command. For example:
:!! Dir
| Note: |
|---|
|
The command is executed on the computer on which sqlcmd is running.
|
-
:XML [ON | OFF]
-
For more information, see "XML Output Format," later in this topic
-
:Help
-
Lists sqlcmd commands together with a short description of each command.
sqlcmd File Names
sqlcmd input files can be specified with the -i option or the :r command. Output files can be specified with the -o option or the :Error, :Out and :Perftrace commands. The following are some guidelines for working with these files:
-
:Error, :Out and :Perftrace should use separate <filename>. If the same <filename> is used, inputs from the commands may be intermixed.
-
If an input file that is located on a remote server is called from sqlcmd on a local computer and the file contains a drive file path such as :out c:\OutputFile.txt. The output file will be created on the local computer and not on the remote server.
-
Valid file paths include: C:\<filename>, \\<Server>\<Share$>\<filename> and "C:\Some Folder\<file name>". If there is a space in the path, use quotation marks.
-
Each new sqlcmd session will overwrite existing files that have the same names.
Informational Messages
sqlcmd prints any informational message that are sent by the server. In the following example, after the Transact-SQL statements are executed, an informational message is printed.
At the command prompt, type the following:
sqlcmd
At the sqlcmd prompt type:
USE AdventureWorks;
GO
When you press ENTER, the following informational message is printed: "Changed database context to 'AdventureWorks'."
Output Format from Transact-SQL Queries
sqlcmd first prints a column header that contains the column names specified in the select list. The column names are separated by using the SQLCMDCOLSEP character. By default, this is a space. If the column name is shorter than the column width, the output is padded with spaces up to the next column.
This line will be followed by a separator line that is a series of dash characters. The following output shows an example.
Start sqlcmd. At the sqlcmd command prompt, type the following:
USE AdventureWorks;
SELECT TOP (2) ContactID, FirstName, LastName
FROM Person.Contact;
GO
When you press ENTER, the following result set is retuned.
ContactID FirstName LastName
----------- ------------ ----------
1 Syed Abbas
2 Catherine Abel
(2 row(s) affected)
Although the ContactID column is only 4 characters wide, it has been expanded to accommodate the longer column name. By default, output is terminated at 80 characters. This can be changed by using the -w option, or by setting the SQLCMDCOLWIDTH scripting variable.
XML Output Format
XML output that is the result of a FOR XML clause is output, unformatted, in a continuous stream.
When you expect XML output, use the following command: :XML ON.
| Note: |
|---|
sqlcmd returns error messages in the usual format. Notice that the error messages are also output in the XML text stream in XML format. By using :XML ON, sqlcmd does not display informational messages.
|
To set the XML mode off, use the following command: :XML OFF.
The GO command should not appear before the XML OFF command is issued because the XML OFF command switches sqlcmd back to row-oriented output.
XML (streamed) data and rowset data cannot be mixed. If the XML ON command has not been issued before a Transact-SQL statement that outputs XML streams is executed, the output will be garbled. If the XML ON command has been issued, you cannot execute Transact-SQL statements that output regular row sets.
| Note: |
|---|
|
The :XML command does not support the SET STATISTICS XML statement.
|