Today I will write about an option that is less known/used in the SSMS tool. The ‘SQLCMD Mode’ option.

What is SQLCMD mode in SSMS?

First let me say that sqlcmd has a command line utility called sqlcmd.exe which allows to connect and run scripts from the command line. This utility exists from SQL Server 2005 and have a lot of switches that you can use!

Many SQL Server folks already have heard other folks talking about it but most of them doesn’t know that exists the same option inside SSMS.

From MSDN:

By using the Database Engine Query Editor in SQL Server Management Studio you can write and edit queries as SQLCMD scripts. You use SQLCMD scripts when you have to process Windows System commands and Transact-SQL statements in the same script.

That seems nice, but what can I do with it?

‘SQLCMD Mode’ provides a wide variety options to use such as:

• Run queries from the same query window but in different instances (using “:connect”)
• Use parameters with (using ”:sqlvar”)
• Special syntax to call other T-SQL scripts from filesystem (using “:r” )
• Call filesystem commands like DIR (using “:!!” )
• And more

And, a little secret, you can mix all these options on the same script if you need to.

You can find all options here.

In this post I will be focusing on the first point, running queries in different instances from the same query window. This comes in handy when we need to run a single query, or multiple queries, against multiple instances and want to see the results in the same results window..

First we need to enable the option

The option is available under Query menu. Just chose ‘SQLCMD Mode’ as shown in the following image.

You can also use ALT+Q+M shortcut to enable/disable it.

Enable SQLCMD Mode

When we enable the option the icon have a light-yellow background to illustrate it enable state

SQLCMD Enabled icon change

What have changed in the query window?

Nothing has changed, but once you start using the specific commands for SQLCMD Mode you will see a gray shade in the background of those lines:

:connect (local)

All specific SQLCMD commands start with colon (:). In this example we will use the :connect option.

This option allows us to connect to a different instance inside the same query windows. The bottom line is, if you can connect to an instance using SSMS you can use that string to connect to other instance using ‘SQLCMD Mode’.

Show in action

 
Important note: You must specify the ‘GO’ command before connect to another instance, otherwise all commands will run on the second instance.

Showing how missed GO change the expected behavior

What about SQL Server Authentication?

By default, the connection will me made using Windows Authentication but if you want you can also connect using SQL Server Authentication.

Performing the same task using SQL Server Authentication will looks like:

Using SQL Server Authentication

Note: If we look to the ‘Messages’ tab we will see the information messages while Connecting/Disconnecting to the instance

Why use this approach instead of CMS?

Maybe the question should be ‘when’ instead of ‘why’.

Central Management Server is another great tool and each option has their pros and cons. But one main difference is that CMS expects that all servers return the same metadata as a result of the executed query.

For instance, if we try to run a simple

SELECT * FROM sys.databases

Through a list of servers with different version (I have 2016, 2014, 2012, 2008, 2005) I receive the following error message:

Running query via CMS

As we can see by the “(XX row(s) affected)” message, the query runs but because the resulting schema did not match the previous output, it will fail.

To run the same query on multiple servers with SQLCMD Mode we will need to duplicate/triplicate/n-plicate the query and put it under each :connect command, however, using this approach if the query return different metadata it will not fail like CMS does.

Running via SQLCMD Mode

The difference is that each query will produce its own result set and we have to include an extra column (@@servername in this example) to know to which instance each result set belongs, while the CMS approach will return a single table with the first column saying to which server that row belongs.

Conclusion:

‘SQLCMD Mode’ has a wider range of use cases than just the one I have shown here. I just want let you know what is possible to do with the :connect command.

This does not replace the CMS which have other purposes and advantages.

Thanks for reading.