Authoring of SQLCMD Queries Using SQL Server Management Studio Query Editor

The SQL Server Management Studio Query Editor has support for SQLCMD scripts authoring and execution. The following section demonstrates how to enable this feature and how to use it.

Turning the SQLCMD Feature On and Off

While you are inside SQL Server Management Studio, SQLCMD mode can be turned on and off for SQL Queries on both a per-window and global basis. To toggle the mode for a particular query window, use the Query>SQLCMD Mode menu item or the corresponding button on the query window toolbar (see Figure 6-3).

Figure 6-3. SQL Server Management Studio toolbar.

[View full size image]

Tips and Tricks

If you find this mode useful, you can make it the default for all SQL Query windows. To do that, go to the Tools>Options dialog (see Figure 6-4) and select Query Execution>SQL Server>General category on the left side of the dialog. After that, check the check box at the bottom of the page on the right side of the dialog that says By default, open new queries in SQLCMD mode.

Figure 6-4. SQL Server Management Studio Options dialog.

[View full size image]

While adding support for SQLCMD syntax to Query Editor, the development team envisioned two primary scenarios. One scenario is to make Query Editor more powerful by supporting new commands in addition to GO. The other one is to enable writing and debugging SQLCMD scripts in Query Editor so that they can be executed later from batch files with the SQLCMD.EXE command line tool.

Colorizing of SQLCMD Commands and Variables

Now you can try using this mode to author and execute in Query Editor a simple query that selects all columns from a table whose name is specified by a SQLCMD variable.

Create a new query window and connect to AdventureWorks. Turn on the SQLCMD mode. After that, type the following in the text editor area of the query window:

:setvar tablename HumanResources.Department
go
select * from $(tablename)
go

Please note the same rules apply as if we were working in console mode, that is, the command should be the first statement on the line.

Notice that the line containing the :setvar SQLCMD command is highlighted. All recognized SQLCMD commands are highlighted this way so that it is clear that the command is not part of T-SQL syntax.

SQLCMD Mode Script Execution in Query Editor

Any highlighted commands are going to be recognized by the execution engine. If you run a query in the Query Editor, the results appear the same way as if you were to execute it from within the console application.

If you turn off the SQLCMD mode for this query window you'll notice that the first line is no longer highlightedit means that query editor won't do any special processing for it while executing the batch:

:setvar tablename HumanResources.Department
go
select * from $(tablename)
go

If you run the query in this mode, execution fails because the SETVAR command is submitted directly to SQL Server Database Engine, and the engine doesn't recognize its syntax.

It is important to know that any definitions and settings introduced by a SQLCMD command during previous query execution are not carried over and remembered for the next execution. This is a significant difference from the command line tool. Query Editor remembers only settings that are specified with UI elements of SSMS. For example, suppose you execute the following:

:setvar tablename HumanResources.Department
go

If after that you execute the following query separately from the preceding execution, you get an error that the tablename variable is not defined:

select * from $(tablename)
go
A fatal scripting error occurred.
Variable tablename is not defined.

To correct this problem you need to execute both batches in one shot:

:setvar tablename Person.Address
select * from $(tablename)
go

It is important to note that all major Query Editor features are usable while queries execute in SQLCMD mode. For example, you can still parse the text and view the graphical execution plan. However, some particular aspects of the Query Editor behavior are slightly different when you are working in this mode. Some of the SQLCMD commands are not supported at all and some have limitations.

SQLCMD Commands Not Supported by Query Editor

The list of SQLCMD commands that are not supported by Query Editor demonstrates the main difference between SQLCMD.exe and SSMS. The former is an interactive command line tool where the interaction with the user happens via Windows console window. The latter has graphical user interfaces and interacts with the user via menu commands and graphical elements. Therefore, not all interactive SQLCMD commands are supported inside Query Editor. What follows is the list of nonsupported commandsQuery Editor issues a warning in the Message tab if it encounters these commands while in SQLCMD mode:

• :serverlist Lists local and SQL Servers on the network.

• :reset Discards the statement cache.

• :perftrace Redirects timing output to a file, stderr, or stdout. This is not supported because Query Editor doesn't have this concept.

• :listvar Lists the set SQLCMD scripting variables.

• :ed Edits the current or last executed statement cache.

• :help Shows the list of supported commands.

When one of these commands is used in a SQLCMD query, Query Editor issues a non-fatal warning into the Messages tab during execution and continues on.

:help
select 1

Scripting warning.
Command Help is not supported. String was not processed.

------------------
1

(1 row(s) affected)

One other important difference between Query Editor and SQLCMD.EXE is that Query Editor doesn't support built in SQLCMD and OSQL variables. If you try specifying them in the query that you execute inside SSMS you'll see an error about an undefined variable:

print '$(SQLCMDSERVER)'
go

A fatal scripting error occurred.
Variable SQLCMDSERVER is not defined.

Redirecting Results and Errors in Query Editor

Query Editor still supports the following SQLCMD commands: :out <destination> and :error <destination>. As explained earlier, their purpose is to redirect the output and error messages respectively into a file, stderr, or stdout location.

Suppose you're working on a query and you want to always have results in a file separate from error messages that should be in a different file. Assuming you have a c:\output folder, you can do the following:

:out c:\output\queryresults.txt
:error c:\output\queryerror.txt
GO
exec sp_who
go
exec sp_who 'foo'

If you execute it, you'll notice that the results of the first sp_who execution will be in the first TXT file, and unless you have a login 'foo,' the error message from the second sp_who execution will be in the second TXT file.

Because SQL Server Management Studio is a UI application and not a command line tool, it can't really support strerr and stdout parameters to these commands. However, it does accept them and redirects subsequent output to the Messages window. For example, executing the following puts the results of exec sp_who 'sa' into the text file and the results of exec sp_who into the Messages window.

:out c:\output\queryresults.txt
GO
exec sp_who 'sa'
GO
:out stdout
GO
exec sp_who

You may notice that it is similar to the Results to File and Results to Text query execution modes that Query Editor has. The major difference is that by utilizing these commands, you can change the result mode dynamically for different parts of the query. This might be useful for large scripts that do multiple logical operations, for which you want to track results independently.