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.
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.
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.
Stops the current batch without executing the batch or returning any value.
Executes the batch and then stops, returning no value.
Executes the batch (including the Query), returns the results of the Query and then stops.
To connect to an instance, instance1, of SQL Server, myserver, by using integrated security, you would include the following command:
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
:SETVAR tablevar Customers
The selectFromTable.sql file contains the following example query: