SQLCMD Mode in SSMS: Variables, :CONNECT and :r Includes

A deployment script with hard-coded server and database names works in test, then points at the wrong place in production. SQLCMD mode gives SSMS scripts variables, file includes, and explicit connections. It can make the release repeatable when each target and failure rule is visible in the script.

Turn On SQLCMD Mode in the Editor

In SSMS, open a query window and select Query, SQLCMD Mode before running the script. SQLCMD commands such as :setvar and :r are editor directives, not T-SQL sent to the Database Engine. Their lines are shaded in the SQLCMD editor. A script that contains them in an ordinary query window will fail or be misread.

I keep a clear banner at the top of release files saying they need the SQLCMD editor setting. It prevents someone from running only the T-SQL portions and assuming the whole deployment executed. Which server and database does the window currently show? Check that before any command with side effects.

Replace Names With Variables

:setvar defines a scripting variable, referenced as $(Name) in later text. SQLCMD substitutes the value before SQL Server parses the T-SQL. That makes quoting important: bracket a database identifier and use a separate literal when printing it. Do not put passwords into setvar lines or logs.

:setvar TargetDb "SalesTest"
USE [$(TargetDb)];
GO
SELECT DB_NAME() AS active_database,
       N'$(TargetDb)' AS expected_database;
GO

Run the SELECT as a preflight and compare the two names. A variable value is text substitution, not a typed T-SQL parameter. Keep variable values from a trusted release manifest and validate them before running dynamic object names. The sqlcmd utility matched $(ABC) to a variable set as Abc in my test, but keep one spelling so the script reads the same in every client.

Use :CONNECT for Explicit Targets

:CONNECT changes the query window's target server. When one release script runs against two servers, put a GO between the last statements for one server and the next connection. Otherwise buffered text can execute on the last connection. Print @@SERVERNAME and DB_NAME after every connect; never rely only on the SSMS status bar.

:CONNECT TestSqlServer
:setvar TestDb "SalesTest"
USE [$(TestDb)];
GO
SELECT @@SERVERNAME AS connected_server,
       DB_NAME() AS connected_database;
GO
:CONNECT ProdSqlServer
:setvar ProdDb "SalesProd"
USE [$(ProdDb)];
GO
SELECT @@SERVERNAME AS connected_server,
       DB_NAME() AS connected_database;
GO

Those are placeholders, not names to paste into a live release. Each target gets its own variable, so no value depends on when a client applies a second :setvar for the same name. Use Windows authentication where the environment supports it. The script should verify expected server identity and fail when it does not match. A manually selected query tab is not a reliable deployment control.

Include Reviewed Files With :r

:r reads another script file into the current SQLCMD script. Keep preflight, schema change, data migration, and verification in separate reviewed files, then include them in an explicit order. Use Windows paths on the machine running SSMS. Relative path handling can depend on where the script is executed, so test the exact release folder and client in rehearsal.

:r .\01-preflight.sql
:r .\02-schema.sql
:r .\03-verify.sql

Each included file needs its own correct GO boundaries. A CREATE OR ALTER PROCEDURE statement, for example, must be in the right batch. Store the files together as one versioned release package and inspect the expanded execution in a test environment. An include that points to an old sibling file can silently deploy old logic.

Stop on the First Error

:on error exit tells SQLCMD to stop on a reported error instead of continuing into later steps. Put it at the top of the release. Use THROW in preflight checks for wrong server, database, or schema state. Some warnings do not become errors, and business validation must still be explicit. A transaction can need a separate rollback strategy if a later batch fails.

:on error exit
IF DB_NAME() <> N'SalesTest'
    THROW 50001, 'Wrong database for test deployment.', 1;
GO

Do not assume a client directive replaces review of transactional boundaries. A script with several GO batches can commit early batches before a later error exits. Document the forward-fix or rollback path for each stage. Test an intentional error in a nonproduction copy and confirm the next include does not run.

Put the Release Together in SQLCMD Mode

A sample top-level script connects to test, switches to the target database, then runs the preflight, change, and verify files. The production file is a copy with ProdSqlServer and SalesProd, run separately after an operator reviews the test result. The same include files are reused, while target values and preflight expectations change.

:on error exit
:CONNECT TestSqlServer
:setvar TargetDb "SalesTest"
USE [$(TargetDb)];
GO
:r .\01-preflight.sql
:r .\02-schema.sql
:r .\03-verify.sql
GO

The included preflight should check the expected target, not merely that a connection succeeded. Run a dry rehearsal with restored production data, then capture output and row counts during the release. I store the exact script package and execution log so a later operator can see what actually ran. SQLCMD mode helps when the release contract is explicit; it cannot make an unsafe change safe by itself.

Keep the Included Files Predictable

Each included file should have a narrow purpose and a precondition. The schema file should not choose its own server; the top-level release sets the target. The verification file should check real object definitions and row counts, not merely print a success message. Use absolute Windows paths when the execution directory is uncertain, or package the files in one tested folder and rehearse from that exact path. Avoid paths that depend on one person's profile.

If a file includes another file, document that dependency in the release manifest. A missing include should stop the process before production is touched. The source of truth is the exact set of files that ran, so preserve their hashes with the release log. An operator should be able to repeat the test without guessing which copy of 02-schema.sql was used.

Test the SQLCMD Mode Stop Rule

Create a deliberate THROW in a nonproduction copy of the second include and run the top-level script. Confirm that the third include and the production connection are not reached. Then remove the test error and perform a clean rehearsal. SSMS and the standalone sqlcmd utility differ in some behaviors; validate with the same client used for the release. The colon commands are client features, so a different runner can ignore or reject them.

Related reading on this blog: SQL SERVER Management Studio and SQLCMD Mode and How to Set Variable and Use Variable in SQLCMD Mode.

SQLCMD mode is not plain T-SQL, it is a client script with explicit batch and stop rules.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.