Disabling variable substitution inside SQLCMD script when using :r syntax

I am using SQLCMD to deploy a set of scripted Agent jobs. I am running these with a SQLCMD script to calls each script using the :r syntax.

It looks like this:

:ON ERROR EXIT
:r $(scriptpath)\Script1.sql
:r $(scriptpath)\Script2.sql
:r $(scriptpath)\Script3.sql

Is there anyway to disable variable substitution with these scripts (equivalent of the /x switch for SQLCMD)?

I want to run Script1.sql, Script2.sql, etc... without variable substitution.

If you want to do the same thing that you are attempting to do within Management Studio, I think the simplest approach is to write a very simple PowerShell script to run the same thing that goes through each file and then executes the file with SQL CMD.

In my example, I have a directory named e:\test. I have a few .sql files in that directory and I want to execute them and disable variable substitution. To do that I would use the following script within powershell:

$Path = "e:\test"
$files = Get-ChildItem e:\test\*.sql
ForEach ($file in $files) {
Invoke-Sqlcmd -ServerInstance localhost -InputFile $file -DisableVariables
}


This obviously takes you out of Management Studio, but it should accomplish the task fairly easily and do so with variable substitution being disabled.

StackExchange Database Administrators Q#115262, answer score: 3