The SQL Server Management Studio Query Editor offers a feature called SQLCMD mode, which allows you to edit and run SQLCMD scripts. While this feature may not be widely known or used, it can be quite handy in certain scenarios. In this article, we will explore the basics of editing SQLCMD scripts using the Query Editor.

Enabling SQLCMD Mode

By default, SQLCMD mode is disabled in the Query Editor. To enable it, go to the Query menu and click on the “SQLCMD mode” item or press ALT+Q+M. If you want SQLCMD mode to be the default, you can go to Tools -> Options -> Query Execution -> SQL Server -> General and check the “By default, open new queries in SQLCMD mode” option.

It’s important to note that SQLCMD mode does not support intellisense or debugging. However, it can still be useful for running and scheduling scripts against multiple servers or databases, or for testing scripts that you plan to run using the command line.

Editing SQLCMD Scripts

SQLCMD scripts contain a combination of SQL statements and SQLCMD commands. SQLCMD commands are preceded by a colon (:) and are used to control the behavior of the script. Only a subset of SQLCMD commands is available within the Query Editor.

Here is an example script that collects data about the SQL Server versions and editions running on servers in a network:

:ON ERROR EXIT
:CONNECT (local)
SET NOCOUNT ON;
SELECT SERVERPROPERTY('ServerName') AS ServerName,
       SERVERPROPERTY('ProductVersion') AS ProductVersion,
       SERVERPROPERTY('ProductLevel') AS ProductLevel,
       SERVERPROPERTY('Edition') AS Edition,
       SERVERPROPERTY('EngineEdition') AS EngineEdition;
GO
:CONNECT SQL2
SET NOCOUNT ON;
SELECT SERVERPROPERTY('ServerName') AS ServerName,
       SERVERPROPERTY('ProductVersion') AS ProductVersion,
       SERVERPROPERTY('ProductLevel') AS ProductLevel,
       SERVERPROPERTY('Edition') AS Edition,
       SERVERPROPERTY('EngineEdition') AS EngineEdition;
GO

In this script, the “:CONNECT” command is used to connect to specific servers and instances. By default, a trusted connection is used, but you can also connect using SQL authentication by providing the necessary environment variables.

It’s important to include the “GO” batch separator between SQL statements. If the “GO” is missing, only the last SELECT statement will be executed, even if the connection is changed.

Capturing Errors and Query Results

You can capture errors and query results in separate files by adding a few lines to the script. Here is an example:

:ON ERROR EXIT
:SETVAR Path "C:\MyLocation\"
:SETVAR FileName "QueryResults.txt"
:OUT $(Path)$(FileName)
:SETVAR ErrPath "C:\ErrLogs\"
:SETVAR ErrFileName "Err.txt"
:ERROR $(ErrPath)$(ErrFileName)
:CONNECT (local)
SET NOCOUNT ON;
SELECT SERVERPROPERTY('ServerName') AS ServerName,
       SERVERPROPERTY('ProductVersion') AS ProductVersion,
       SERVERPROPERTY('ProductLevel') AS ProductLevel,
       SERVERPROPERTY('Edition') AS Edition,
       SERVERPROPERTY('EngineEdition') AS EngineEdition;
GO
:CONNECT SQL2
SET NOCOUNT ON;
SELECT SERVERPROPERTY('ServerName') AS ServerName,
       SERVERPROPERTY('ProductVersion') AS ProductVersion,
       SERVERPROPERTY('ProductLevel') AS ProductLevel,
       SERVERPROPERTY('Edition') AS Edition,
       SERVERPROPERTY('EngineEdition') AS EngineEdition;
GO

In this modified script, the “:SETVAR” command is used to define scripting variables. These variables can be called later in the script by prefixing them with $ and including them in parenthesis, like $(variable_name).

The “:OUT” command redirects the query results to an output file, while the “:ERROR” command redirects error messages to another file. Both files will be recreated each time the script is run.

Executing Scripts Against Multiple Databases

You can also execute scripts against multiple databases using the “:R” command. Here is an example:

:ON ERROR EXIT
:SETVAR Path "C:\MyLocation\"
:SETVAR ScriptToExecute "revoke.sql"
:SETVAR ErrPath "C:\ErrLogs\"
:SETVAR ErrFileName "Err.txt"
:ERROR $(ErrPath)$(ErrFileName)
USE TestDB1
:R $(Path)$(ScriptToExecute)
USE TestDB2
:R $(Path)$(ScriptToExecute)

In this example, the “revoke.sql” script is executed against two databases. The “:R” command parses the .sql file and loads it into the statement cache, allowing it to be executed against the specified databases.

Additional Features

In SQLCMD mode, you can also execute operating system commands using the “!!” prefix. For example:

EXEC master.dbo.xp_cmdshell systeminfo
GO
!!systeminfo

Even if the xp_cmdshell is disabled on your machine, the “!!systeminfo” command will still return the results.

While SQLCMD mode may not be the most user-friendly feature in the Query Editor, it can be quite useful for certain administrative tasks, especially in simpler environments. Give it a try and see if it helps streamline your SQL Server scripting workflow.