Disabling variable substitution inside SQLCMD script when using :r syntax

Question

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.

Answer 1

I think I understand the problem. Your SQL Agent scripts have sqlcmd variables in them which you do not want to set whilst deploying the scripts, only when the job runs. I imagine you have a job step like this for example:

When you run the script you receive an error saying the variable is not defined, eg

A fatal scripting error occurred.
Variable myVar is not defined.

There are a couple of workarounds for this.

1. declare the sqlcmd variable in your deployment script and set its value to how the variable would look in the script. So in my simple example I have a variable called myVar so I set its value to "$(myVar)" like this:

   :setvar myVar "$(myVar)"
   

2. Run the sqlcmd executable with variable substitution disabled from within SQL Server Management Studio. Placing two exclamation marks (!!) or (bang-bang) before commands in sqlcmd mode allows you to run that command as if from the command-line, eg

   !!sqlcmd -i "$(scriptpath)\temp.sql" -S.\sql2014 -x
   

Basically you are just replacing the :r for a !!sqlcmd and it should work.

HTH

Answer 2

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.