Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

An Introduction to the SQLCMD Mode in SSMS (SQL Spackle)

By Wayne Sheffield,

“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

Total article views: 7705 | Views in the last 30 days: 25
 
Related Articles
FORUM

Batch Variable in sqlcmd

Batch Variable in sqlcmd

BLOG

SQLCMD – Explicit and Implicit calls – Examples

The scripting variables can be defined explicitly by using the setvar command or implicitly by using...

FORUM
FORUM

SQLCMD how to use drive and path in scripting variables

try creating a scripting variable to hold a value like D:\data

ARTICLE

Introduction to SQLCMD

SQL Server 2005 has deprecated osql and isql as command line tools and replaced them with SQLCMD. Ha...

Tags
sqlcmd    
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones