In SQL Server Management Studio what is SQLCMD mode?

In SQL Server Management Studio I would like to know what is SQLCMD mode?

I did some more research, so here's my understanding of this to extend what has been written so far:

What is SQLCMD

SQLCMD.exe is a console utility included in the instalation of SQL Server 2005 and higher. You can typically find it in a path like c:\Program Files\Microsoft SQL Server\100\Tools\Binn\SQLCMD.EXE.

It is a simple scripting environment that allows automation of tasks related to SQL server. For example, you can write and execute a script that will login to a specific instance of SQL Server, execute scripts from a given directory on this connection and store the output in a specified file.

Invoke-Sqlcmd cmdlet was introduced with SQL Server 2008 as a mean to replace this tool with a standardized, Powershell-based approach, preserving most of the original syntax and functionality.

What is SQLCMD mode in SSMS

In SSMS, SQLCMD mode is a script execution mode that simulates the sqlcmd.exe environment and therefore accepts some commands that are not part of T-SQL language. Unlike sqlcmd.exe, it contacts the database using SqlClient (the same way as SSMS), not ODBC data provider, so in some aspects it might have different behaviour than sqlcmd.exe.

Executing scripts in SQLCMD mode allows to use commands typical to sqlcmd.exe environment. However, there's no IntelliSense or debugging support for SQLCMD mode, so maintaining scripts that mix clean T-SQL with SQLCMD-specific code can be a pain. Therefore, it should be used only when it's necessary.

Example use case

Let's suppose that a company has a naming convention for databases that include environment in the name, eg: MyDb_Prod, MyDb_Test, MyDb_Dev. This convention might be used to minimize chance of mistakes.

When a developer writes a T-SQL script, it will have to be executed in different environments in deployment/testing process, which would require many versions of the code:

 SELECT *
 FROM [MyDb_Dev].[dbo].[MyTable1] -- MyDb_Dev -> MyDb_Test -> MyDb_Prod

Instead, we can assume that database name will be provided as a SQLCMD variable in deployment process and have exactly the same file deployed to all environments:

 -- :setvar databaseName "MyDb_Dev" -- uncomment for testing in SSMS

 SELECT *
 FROM [$(databaseName)].[dbo].[MyTable1]

(in this simple example database name could be omitted altogether, but if you have cross-database joins, using database name is necessary)

Exactly what it sounds like.

It is a mode that lets you author SQLCMD scripts.

From MSDN - Editing SQLCMD Scripts with Query Editor:

> To use the Database Engine Query Editor to write or edit SQLCMD scripts, you must enable the SQLCMD scripting mode.

In SQL Server Management Studio, set this via the Query menu (Query -> SQLCMD Mode).

"You use SQLCMD scripts when you have to process Windows System commands and Transact-SQL statements in the same script."

"By default, SQLCMD mode is not enabled in the Query Editor. You can enable scripting mode by clicking the SQLCMD Mode icon in the toolbar or by selecting SQLCMD Mode from the Query menu."

Reference: MSDN

Message Transact-SQL IntelliSense is not active for this editor because the editor is in SQLCMD mode.

I was publishing SQL Server Project from Visual Studio to Database to synchronize changes and got an error above. Publishing was failing. I closed all open files in VS and it resolved the issue.

I hope real life example explained why people use SQLCMD mode.