Editing SQLCMD Scripts in the Transact-SQL Editor

  • Article

When you write or edit scripts in the Transact-SQL editor, you can use a mix of Transact-SQL and SQLCMD commands if you first enable SQLCMD mode. For more information, see How to: Enable SQLCMD mode in the Transact-SQL Editor.

Writing and Editing SQLCMD Scripts

If you want to include SQLCMD commands and Transact-SQL commands in the same script, you must ensure that the script conforms to the following requirements:

  • SQLCMD commands must be the first statement on a line.

  • Only one SQLCMD command is permitted on each line.

  • SQLCMD commands can be preceded by comments or white space.

  • Single line comment characters are two hyphens (--) and must appear at the beginning of a line. Comment characters prevent SQLCMD commands from being executed.

  • Before each SQLCMD command, you must place a colon at the start of a line to clearly distinguish it from Transact-SQL commands. The only exceptions are the exit and !! commands, for which colons are optional.

  • You can use environment variables and variables that are defined as part of a SQLCMD script, but you cannot use built-in SQLCMD variables.

Supported SQLCMD Syntax

You can use the following 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 for Visual Studio. The file is 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 that is defined in Tools, Options. You can use :r in a pre-deployment or post-deployment script to include other scripts.

  • :setvar Variable Value
    Defines scripting variables. Scripting variables have the following format: $(Variable). Variable names are case insensitive. If a variable that is defined by using :Setvar has the same name as an environment variable, the variable that is defined by using :setvar takes precedence. Variable names must not contain 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, you must enclose the value in quotation marks. If a value for a scripting variable is not specified, the scripting variable is dropped.

  • :connect ServerName[\InstanceName] [-l Timeout] [-U UserName [-P Password]]
    Connects to an instance of SQL Server and closes the current connection after the number of seconds specified by Timeout has elapsed. You can specify "-l 0" for an infinite timeout or a positive number of seconds, such as "-l 30". If you provide neither options nor environment variables for UserName and Password, Windows authentication mode is used to log on. When running the connect SQLCMD command in the Transact-SQL editor, you will still be prompted to connect to a database server with the Connect to Database Engine window.

  • :on error [exit | ignore]
    Sets the action to be performed when an error occurs during script or batch execution. If you use the exit option, the execution exits with the appropriate error value. If you use the ignore option, the Transact-SQL editor ignores the error and continues to execute the batch or script. An error message is printed by default.

  • :out Filename | stderr | stdout
    Redirect all query results to the file specified by Filename or to the Messages tab. (In Visual Studio, stderr and stdout send output to the Messages tab.) The out command can appear multiple times in a script. By default, output is sent to the Messages tab. If a Filename is specified, Visual Studio creates or opens a file to receive the query results. If the file already exists, it will be truncated to zero bytes prior to writing the output.

  • :error Filename | stderr | stdout
    Redirect all error output to the file specified by Filename or to the Messages tab. (In Visual Studio, stderr and stdout send output to the Messages tab.) The error command can appear multiple times in a script. By default, error output is sent to stderr. If a Filename is specified, Visual Studio creates or opens a file to receive the error message. If the file already exists, it will be truncated to zero bytes prior to writing the error message.

  • [:]exit
    Stops the current batch without executing the batch or returning any value.

  • [:]exit()
    Executes the batch and then stops, returning no value.

  • [:]exit(Query)
    Executes the batch (including the Query), returns the results of the Query and then stops.

  • [:]!! Operating system command
    Executes operating system commands on the computer that is running Visual Studio. To execute an operating system command, start a line with two exclamation marks, !!, followed by the operating system command.

Note

Because you are not starting SQLCMD from the command line, there are some limitations when running the Transact-SQL editor in SQLCMD Mode. You cannot pass in command-line parameters such as variables, and, because the Transact-SQL editor does not have the ability to respond to operating system prompts, you must be careful not to execute interactive statements.

SQLCMD commands not listed above are not supported in the Transact-SQL editor. When a script containing SQLCMD keywords that are not supported is executed, the Transact-SQL editor will send a message to the destination for each unsupported keyword that says "Command <unsupported command> is not supported. String was not processed.". The script will execute successfully, but the unsupported commands will be ignored.

Color Coding in SQLCMD Scripts

When SQLCMD Mode is enabled, scripts will be color coded. The color coding for Transact-SQL keywords will remain the same. SQLCMD commands are presented with a shaded background.

Examples

The following example uses SQLCMD statements to create an output folder named c:\TempSqlcmdOutput and an output file named testoutput.txt. The example also executes two Transact-SQL SELECT statements and another SQLCMD command to print out the current directory of the Visual Studio program. The resultant file contains the message output from the DIR statement, followed by the results output from the Transact-SQL statements. This example assumes a connection to an instance of SQL Server has already been established and SQLCMD Mode has been enabled.

!!mkdir c:\TempSqlcmdOutput
:out c:\TempSqlCmdOutput\testoutput.txt
select @@VERSION as 'Server Version'
!!dir
GO
select @@SERVERNAME as 'Server Name'
GO 4

See Also

Tasks

How to: Execute a Query

How to: Set Options for Query Execution and Results

Concepts

Query Results Overview