SQLCMD Mode is a powerful feature in SQL Server Management Studio (SSMS) that allows you to create, test, and execute SQLCMD commands or scripts directly in the query editor. This feature has been available since SQL Server 2005 and provides a convenient way to work with SQLCMD scripts within SSMS.

To enable SQLCMD Mode in SSMS, you can either click on the “SQLCMD Mode” option under the Query menu or use the keyboard shortcut ALT+Q+M. By default, SQLCMD Mode is turned off, but you can set it to open new queries in SQLCMD Mode by default through the SSMS Options menu.

When working in SQLCMD Mode, the IntelliSense and Transact-SQL debugger are turned off in the Database Engine Query Editor. However, you can still execute both SQLCMD and T-SQL statements. SQLCMD commands are automatically highlighted in gray, while T-SQL statements appear normal.

Let’s take a look at some of the SQLCMD script keywords that the Database Engine Query Editor supports:

:CONNECT ZIVKO\ZIVKO2014
:OUT C:\Users\Marko\Data.txt

USE AdventureWorks2014;
SELECT a.City, a.PostalCode
FROM Person.Address a

When executing the above code, the SQLCMD commands are automatically recognized and highlighted, while the T-SQL statements are executed as usual. This allows you to seamlessly work with both types of statements in the same script.

Most SQLCMD commands begin with a colon (:) followed by the command keyword. However, for a few commands like QUIT and EXIT, the colon can be omitted. For example, both “:QUIT” and “QUIT” will work the same way.

SQLCMD Mode also provides several useful script keywords for managing connections, redirecting query results, defining variables, handling errors, and more. Here are a few examples:

:CONNECT (local)
:OUT C:\Users\Marko\Data.txt
:SETVAR username "Marko"
:SETVAR login "Zivko"
:ERROR Errors.txt
:ON ERROR EXIT
:QUIT

These keywords allow you to connect to a SQL Server instance, redirect query results to a file, define variables, handle errors, and exit the SQLCMD session. They provide flexibility and control over your SQLCMD scripts.

SQLCMD Mode is particularly useful when you need to execute the same code on multiple databases or servers. By switching to SQLCMD Mode and using script keywords, you can easily automate repetitive tasks and save time.

In conclusion, SQLCMD Mode in SQL Server Management Studio is a powerful feature that allows you to work with SQLCMD commands and scripts directly in the query editor. It provides a seamless integration of SQLCMD and T-SQL statements, along with various script keywords for managing connections, redirecting results, defining variables, handling errors, and more. By leveraging SQLCMD Mode, you can enhance your productivity and efficiency when working with SQL Server.