Sql-server – Disabling variable substitution inside SQLCMD script when using :r syntax

sql serversqlcmd

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.

Best Answer

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:

enter image description here

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

Related Question