Have you ever had a script where you needed to run parts of it on different instances of SQL Server (for example, to set up database mirroring)? Wouldn’t it be so much easier if you could just have commands, within the script, to connect to the various servers and run that portion of code? Of course it would! And once again, SSMS comes to the rescue with SQLCMD mode.
With any query window open, on the Query menu is an item for, you guessed it, SQLCMD mode.
This puts your query into SQLCMD mode, where it recognizes certain commands. It also will turn off intellisense in this window. SQLCMD commands will be highlighted in grey (as can be seen in the screen shots throughout the rest of this post).
The commands that it recognizes are:
|
1 2 3 4 5 6 7 8 9 10 |
:SETVAR :CONNECT :RESET :ERROR :OUT :QUIT :EXIT :R :ON ERROR EXIT :!! |
Notice that all of the SQLCMD commands start with a colon (:)
The complete reference for how all of these commands work is at //msdn.microsoft.com/en-us/library/ms162773.aspx. I will cover just a few of them in this blog post.
SETVAR allows you to set a variable for use throughout the script. Its syntax is SETVAR . For instance, “:SETVAR SourceServer .\SQL2005” will set the variable SourceServer to “.\SQL2005”, which is the name of the SQL 2005 instance on my laptop.
To use the variable anywhere throughout the script, it is referenced like $(SourceServer). For instance, PRINT $(SourceServer):
This screen shot shows the highlighting of the SQLCMD command, and the results from the above two lines of code.
When displaying variables with the $() syntax, you can also display environmental variables. Note that these will be case sensitive. For instance:
This works, since the environment variable is lowercase. However, change it to upper case and you end up with:
Unlike TSQL variables, which are available only within the current batch, SQLCMD variables are available throughout the script, even across multiple batches.
The :CONNECT command will connect to a specified server. You just specify the server name (and instance if it is a named instance). It’s been my experience that this is not applied until the next batch separator command is used (normally GO), so I always use that immediately afterwards. For instance:
An example of why I use the go immediately can be seen here – the last CONNECT in the batch is the one that is used for the entire batch:
When you are using multiple CONNECTs in a script, ensure that they are various connects are separated by a GO – at a minimum keep the CONNECT with the batch of SQL that it needs to run:
The next part combines both of these together. This can be especially useful when you may need to switch back and forth between different servers. Here we will use a variable to set the SourceServer variable, and then use that within the CONNECT command to connect to that server.
The final SQLCMD that I will cover is the :!! Command. This shells out to DOS on the computer that you are running SSMS on (not necessarily the server that you are connected to), and can run any DOS command that you have permission to run on that computer. If you were connected to a server from your workstation, it would shell out to your workstation. For instance, let’s run a directory of the windows directory:
In conclusion, the SETVAR and CONNECT SQLCMD commands will add a lot of flexibility to your scripts. You can even incorporate template variables in with these.
This post is for day eight of my month-long blog series “A Month of SSMS Tips”. I have a landing page for the series at bit.ly/MonthOfSSMS. Please visit this page for an easy place to quickly view all of the other tips in this series.
