The Art of SQL Server Database Administration, Development, and Career Skills for the Technically Minded
Today we look at querying in SQLCMD mode within SSMS. SQLCMD is a command line interface that allows you to issue queries against an instance of SQL Server right from the prompt. This has been around for a long time and the savvy DBA will keep it in their quiver as a way to talk to a server through a low-overhead interface. There are slight differences and nuances to the way SQLCMD works with SQL Server including additional options in syntax. Because of this, SSMS allows you to work with the server in SQLCMD mode.
To activate SQLCMD mode when working in SSMS select it from the Query menu item as shown here. As indicated in the intro, this mode exists to mimic the differences between working with queries the default SSMS way vs. the constructs available in SQLCMD.
Why are their differences in the way SQLCMD works with SQL Server over the SSMS interface. Why is SQLCMD mode necessary?
The first major difference is that SSMS uses the .Net SQLClient provider for SQL Server while SQLCMD runs across the ODBC provider. As these are two different providers right off the bat there may be different default set options in one vs the other – especially if you have modified the set options in your SSMS install.
Q: So does running SQLCMD mode in SSMS switch to the ODBC provider then?
A: Well, no – it’s still running across the .Net provider with the same options you’ve specified in SSMS.
Q: So I ask again, what does SQLCMD mode get me?
A: Keep reading
The second major difference is that SQLCMD has many commands unique to it. Because SQLCMD is intended as a command line tool it features syntax that allows it to work much like an old MS-Dos batch file with commands for declaring variables, setting up output files, and a variety of other actions. If fact, sqlcmd can actually run anything available to the command line through its ” :!! ” command. This means it can run dos commands, PowerShell, whatever. See below screen shot of SSMS with a SQLCMD script in SQLCMD mode.
A few weeks ago I wrote a “for fun” article on trying to discover if my SQL Server was in daylight savings time or not. SQL Server doesn’t have an internal means of knowing this, but powershell can find out. SQLCMD is a great way to run batches of operations that rely on both SQL Server and other command line operations such as external executables, scripts, batch files, or even PowerShell.
Truth be told PowerShell on it’s own is probably a much better way to run all these commands – including SQL Statements – but many organizations have legacy scripts designed for SQLCMD so it remains relevant. I only use PowerShell in this way above to demonstrate “what’s possible”.
This then is the real benefit of SQLCMD mode. SSMS is an infinitely more convenient environment for developing TSQL than say notepad, so having the ability to write, modify, or troubleshoot your SQMCMD scripts in SSMS is a real plus. With that said, it would be nice if you could also mimic the ODBC connection used by the actual sqlcmd.exe utility.
Lets go through a few of its features and limitations within SSMS. While SSMS offers a SQLCMD mode not 100% of everything you can do in SQLCMD is supported in SSMS. There is also a flip side. Neither intellisense nor debugging work when SSMS is running this way. It’s still better than notepad however.
The standard TSQL color coding still works – additionally SQLCMD statements are highlighted in gray to distinguish them from standard TSQL. Notice highlighting pictured here.
There is quite a bit you can do with SQLCMD outside of just TSQL. MSDN documents all of these additional constructs and commands so I’m not going to go through all of them. I just point out a couple of the most useful aspects of SQLCMD however.
:Setvar allows you to declare a variable for the context of the SQLCMD. Big deal, your thinking, what’s wrong with the TSQL Declare statement? The difference is that a variable in TSQL isn’t available to anything other than the TSQL batch where it was declared. :Setvar can be used across batches and command line commands across the entire SQLCMD script. See the simple example above where a table name is put into a variable and then referenced in a select statement.
Note that $(<variable>) is the mechanism for calling SQLCMD variables.
:Connect allows you to connect to an entirely different SQL Server instance and run additional TSQL batches. A single SQLCMD script can only have one connection open at a time, each :Connect statement closes the connection open prior, but nothing is stopping you from switching back and forth.
:!! This allows you to specify something other than TSQL or SQLCMD statements that is valid at a command line. SSMS will run anything that SQLCMD can – you just need to be aware that if you execute a command line option that requires further interaction (think hit any key prompt) SSMS does not support this.
:Out is cool because you can redirect the results of TSQL statements following the Out command to a text file on the file system. We talked about ways to do this yesterday as well but the nice thing about doing it in SQLCMD is statements that follow can do additional actions with that text file such as copying it, appending it, or otherwise leveraging it. Dynamic commands based on query results and called as batch files comes to mind.
To spark your imagination here is that same script I showed above using SQLCMD to send TSQL results to an output file and then execute a command line PowerShell statement against that same file. Note usage of the !! and :OUT commands.
If any of this intrigues you I highly recommend learning the syntax and following through the samples on the MSDN link provided. There are a lot more commands than those I’ve mentioned here.
PowerShell is undoubtably taking the place of significant SQLCMD usage in the wild but if you find yourself in a situation where you need to work with it, SSMS provides a way.