SQLCMD, command-line variables and script :setvar

Question

When running SQLCMD.exe and providing command-line arguments for scripting variables, I expect that the values provided on the command-line will override those defined in the SQL script file.

e.g.

Given the following SQL Script:

:setvar XXX "SQL script"
print '$(XXX)'

And the command line:

sqlcmd.exe -S <Server> -d <Database> -E -b -i <Script> -v XXX="Batch script"

I expect the output to be:

Batch script

However the output is:

SQL script

Is this the intention, or must the :setvar statements in the SQL script be removed?

I provided the :setvar statements in script, so I can edit/test the script in SQL Management Studio with SQLCMD mode, but run the scripts from the command-line in my test and production environments.

Answer 1

This appears to be by design; somebody has already raised a change request on Connect: http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=382007

The only way around the issue I can see would be to comment out (or otherwise remove) the :setvar commands on release.

Answer 2

i think it's intentional. currently the setvar statement in the .sql script has the highest precedence.