3

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.

  • I don't completely understand what you are trying to do. Obviously you have a directory $(scriptpath) that contains a bunch of scripts that you want to run for deployment of SQL Agent Jobs. If you disable the variable substitution then SQLCMD won't know where to go to run these files. What is the end result you are looking to accomplish? – mskinner Sep 16 '15 at 19:58
  • I want to run Script1.sql, Script2.sql, etc... without variable substitution. – msgisme Sep 16 '15 at 20:54
3

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

3

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.