Commands in the sqlcmd utility

Applies to: SQL Server Azure SQL Database Azure SQL Managed Instance Azure Synapse Analytics Analytics Platform System (PDW) SQL database in Microsoft Fabric

The sqlcmd utility lets you enter Transact-SQL statements, system procedures, and script files.

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

¹ Not supported on Linux or macOS.

Be aware of the following when you use sqlcmd commands:

• All sqlcmd commands, except GO, must be prefixed by a colon (:).

  Important

  To maintain backward compatibility with existing osql scripts, some of the commands are recognized without the colon, 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 can't be followed by a Transact-SQL statement or another command.

• Commands are executed immediately. They aren't 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.
• Defining an environment variable before you run 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 can't 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 isn't specified, the scripting variable is dropped.

:Listvar

Displays a list of the scripting variables that are currently set.

Output commands

:Error <filename> | STDERR | STDOUT

Redirect all error output to the file specified by filename, to stderr or to stdout. The :Error command can appear multiple times in a script. By default, error output is sent to stderr.

• filename

  Creates and opens a file that receives the output. If the file already exists, it's truncated to zero bytes. If the file isn't available because of permissions or other reasons, the output isn't switched, and is sent to the last specified or default destination.

• STDERR

  Switches error output to the stderr stream. If output has been redirected, the target to which the stream is redirected receives the error output.

• STDOUT

  Switches error output to the stdout stream. If output has been redirected, the target to which the stream is redirected receives 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's 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's 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 is printed.

[:]QUIT

Causes sqlcmd to exit.

[:]EXIT [ ( statement ) ]

Lets you use the result of a SELECT statement as the return value from sqlcmd. If numeric, the first column of the last result row is converted to a 4-byte integer (long). MS-DOS, Linux, and macOS pass the low byte to the parent process or operating system error level. Windows 2000 and later versions 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 exits without a return value.

Here's a list of EXIT formats:

• :EXIT

  Doesn't execute the batch, and then quits immediately and returns no value.

• :EXIT( )

  Executes the batch, and then quits and returns no value.

• :EXIT(query)

  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 quits, and returns the message ID back to the client. For example:

RAISERROR(50001, 10, 127)

This error causes 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, and sqlcmd defines the following additional return values:

GO [count]

GO signals both the end of a batch and the execution of any cached Transact-SQL statements. The batch is executed multiple times as separate batches. You can't declare a variable more than once in 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. filename is read relative to the startup directory in which sqlcmd was run.

If the file contains Transact-SQL statements that aren't followed by GO, you must enter GO on the line that follows :r.

The file will be read and executed after a batch terminator is encountered. You can issue multiple :r commands. The file can include any sqlcmd command, including the batch terminator GO.

: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.

Timeout options:

The SQLCMDSERVER scripting variable reflects the current active connection.

If timeout isn't 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 is prompted to enter a password. Users aren't prompted if the SQLCMDUSER or SQLCMDPASSWORD environment variables are set. If you don't provide options or environment variables, Windows Authentication mode is used to sign in. For example, to connect to an instance, instance1, of SQL Server, myserver, by using integrated security you would use the following command:

:connect myserver\instance1

To connect to the default instance of myserver using scripting variables, you would use the following settings:

: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

:XML [ ON | OFF ]

For more information, see XML Output Format and JSON Output Format in this article.

: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 values. If the same filename is used, inputs from the commands might 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 is 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's a space in the path, use quotation marks.

• Each new sqlcmd session overwrites existing files that have the same names.

Informational messages

sqlcmd prints any informational message that is sent by the server. In the following example, after the Transact-SQL statements are executed, an informational message is printed.

Start sqlcmd. At the sqlcmd command prompt, type the query:

USE AdventureWorks2022;
GO

When you press ENTER, the following informational message is printed:

Changed database context to 'AdventureWorks2022'.

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 column separator 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 is 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 query:

USE AdventureWorks2022;

SELECT TOP (2) BusinessEntityID,
               FirstName,
               LastName
FROM Person.Person;
GO

When you press ENTER, the following result set is returned.

BusinessEntityID FirstName    LastName
---------------- ------------ ----------
285              Syed         Abbas
293              Catherine    Abel
(2 row(s) affected)

Although the BusinessEntityID column is only four characters wide, it expands to accommodate the longer column name. By default, output is terminated at 80 characters. This width 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.

To set the XML mode to off, use the following command: :XML OFF.

The GO command shouldn't 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 can't be mixed. If the :XML ON command wasn't issued before a Transact-SQL statement that outputs XML streams is executed, the output is garbled. Once the :XML ON command is issued, you can't execute Transact-SQL statements that output regular row sets.

JSON output format

When you expect JSON output, use the following command: :XML ON. Otherwise, the output includes both the column name and the JSON text. This output isn't valid JSON.

To set the XML mode to off, use the following command: :XML OFF.

For more info, see XML Output Format in this article.

Use Microsoft Entra authentication

Examples using Microsoft Entra authentication:

sqlcmd -S Target_DB_or_DW.testsrv.database.windows.net  -G  -l 30
sqlcmd -S Target_DB_or_DW.testsrv.database.windows.net -G -U bob@contoso.com -P MyAzureADPassword -l 30

sqlcmd -S Target_DB_or_DW.testsrv.database.windows.net  -G  -l 30
sqlcmd -S Target_DB_or_DW.testsrv.database.windows.net -G -U bob@contoso.com -P MyAzureADPassword -l 30

sqlcmd -S Target_DB_or_DW.testsrv.database.windows.net  -G  -l 30
sqlcmd -S Target_DB_or_DW.testsrv.database.windows.net -G -U bob@contoso.com -P MyAzureADPassword -l 30

Related content

• sqlcmd utility
• Check installed version of sqlcmd utility
• Start the sqlcmd utility
• Execute T-SQL from a script file with sqlcmd
• Use sqlcmd