“SQL Spackle” is a collection of short articles written based on multiple requests for similar code.  These short articles are NOT meant to be complete solutions.  Rather, they are meant to “fill in the cracks”.

--Phil McCracken

Introduction

I would venture to say that almost everyone reading this has heard of the SQLCMD utility and knows that it can be used to run T-SQL statements, system procedures and script files from a command prompt. Many of you have even used it. But one thing that I have noticed is that few people realize that you can run SQLCMD scripts from within SSMS itself. This lends itself to quite a bit of power that many people could utilize that they aren’t. While all of the features of the SQLCMD command-line utility are not available when running a query in SQLCMD mode in SSMS, many are.

Activating SQLCMD Mode

Before you can work with SQLCMD, you have to activate it for your query. Open up a new query window, and then select SQLCMD Mode from the query menu:

Note that when you have enabled a query to use the SQLCMD mode, that Intellisense and the T-SQL debugger are both disabled. The SQLCMD commands will be highlighted in a grey color, as will be shown later.

The SQLCMD commands

The first SQLCMD command to show you is CONNECT. This allows you to connect to a specified SQL Server. Note that all SQLCMD commands are prefixed with a colon.

On my computer, I have four instances of SQL Server installed as can be seen in the following screen shot:

The first thing I want to do is to connect to each of these servers and run a select statement.

(Here you can see the highlighting of the SQLCMD commands.) When I run this script, I get the following output:

Pretty cool – one script can connect to multiple SQL Servers and run statements on each connection. Unlike having one query that can run on multiple connections, each server connected to can run different statements. And you’re not limited to instances on this computer – as long as you have access to that instance from the computer that you are using, you can connect to it. CONNECT defaults to using Windows Authentication (and this is the recommended method); however you can specify the login and password with parameters. Note that after every CONNECT command I have a GO statement – I have found that this is necessary. If the GO statements are removed, you end up with all of these being the result of the last server specified in the CONNECT statement.

Variables

Next up is variables – you can set variables through the :SETVAR command. Once a variable is set, it is referenced in the format $(VariableName). The variables can be used anywhere in the script. For example:

:SETVAR SourceServer .\SQL2012
:CONNECT $(SourceServer)
GO
SELECT @@SERVERNAME, '$(SourceServer)';
GO

Here we used the variable both in the CONNECT statement, and in the select statement. This returns the following result:

You’re not limited to just the variables that you define with SETVAR. You can also utilize variables in the Operating System. The precedence of resolving variables is:

1. System-level environmental variables
2. User-level environmental variables
3. Command shell assignments at the command prompt prior to running the SQLCMD command line utility via SET Variable=Value syntax.
4. Variables assigned with the SQLCMD command line utility with the –v argument.
5. Variables assigned with :SETVAR.

This means that you can run:

SELECT '$(windir)'

Which will return (well, at least on my system – if Windows is installed elsewhere on yours, you’ll get a different result.):

Variables defined with the SETVAR command are case insensitive. However, environment variables are case sensitive, so the following will generate an error:

SELECT '$(WINDIR)'

As you can see, with just these two SQLCMD statements, a lot of power has been unleashed. I like to combine these with another tool not utilized very often – Template Variables. The following example uses the :SETVAR command with a Template Variable.

:SETVAR SourceServer <ProductionServer, sysname,>
:CONNECT $(SourceServer)
GO
SELECT @@SERVERNAME;
GO

By invoking the “Specify Values for Template Variables” dialog (CTRL+SHIFT+M, or the third menu item in the first screenshot in this article of the Query menu), you can specify what the server is change all the variables at one place simply.

Other SQLCMD commands

But wait – there’s more! (I’ve always wanted to use that)

You can also shell out to the OS and run commands – even if xp_cmdshell is disabled. This is performed via the SQLCMD command !!. The command specified is run on the computer on which SQLCMD is running.

An example:

:!! DIR "$(windir)"

Other SQLCMD commands that can be utilized in SSMS are:

:ON ERROR EXIT
:RESET
:ERROR
:OUT
:QUIT
:EXIT
:R

Read about how all of these commands work (as well as the SQLCMD command line utility) at: http://msdn.microsoft.com/en-us/library/ms162773.aspx

Saving SQLCMD scripts

You can save your SQLCMD script just like you would save your other scripts – on the File menu, select Save As. Note that this does not save the SQLCMD mode for the script, so when you open this saved file in the future, you need to Activate SQLCMD mode again for it.

Uses for SQLCMD scripts

Any time you are doing actions that involve multiple SQL Server instances, SQLCMD scripts can let you have one script for the entire task instead of one script for each server. Examples: setting up Transaction Log Shipping or Database Mirroring. Retrieving the most recent backup file from a production server and restoring it on a test server. You get the idea.

References

http://msdn.microsoft.com/en-us/library/ms174187.aspx

http://msdn.microsoft.com/en-us/library/ms162773.aspx