Editing SQLCMD Scripts in the Transact-SQL Editor
When you write or edit scripts in the Transact-SQL (T-SQL) editor, you can use a mix of T-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 T-SQL commands in the same script, you must ensure that the script conforms to the following requirements:
You must place SQLCMD commands as the first statement on a line.
You cannot place more than one SQLCMD command on each line.
You can precede SQLCMD commands by comments or white space.
You can use comment characters to prevent SQLCMD commands from being executed.
You must use two hyphens at the start of a line to form each single-line comment character.
You must place a colon before each SQLCMD command to clearly distinguish it from T-SQL commands.
You can use environment variables and variables that are defined as part of a SQLCMD script, but you cannot use built-in SQLCMD variables.
Using SQLCMD Syntax
You can use the following commands:
- :r FileName
Parses additional T-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 Microsoft 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.
- :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 T-SQL editor ignores the error and continues to execute the batch or script. An error message is printed by default.
- :listvar
Displays a list of the scripting variables that are currently set. Only scripting variables that were set in the current session in SQLCMD mode appear.
- :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.
Examples
To connect to an instance, instance1, of SQL Server, myserver, by using integrated security, you would include the following command:
:connect myserver\instance1.
To connect to the default instance of myserver using scripting variables, you would include the following command:
:setvar myusername test
:setvar myservername myserver
:connect $(myservername) $(myusername)
To execute a simple query stored in the selectFromTable.sql file in C:\Files, you would include the following command:
:SETVAR tablevar Suppliers
:r C:\Files\selectFromTable.sql
GO
:SETVAR tablevar Customers
:r C:\Files\selectFromTable.sql
GO
The selectFromTable.sql file contains the following example query:
SELECT * FROM $(tablevar)
GO