20

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.

Cade Roux
  • 88,164
  • 40
  • 182
  • 265
VirtualStaticVoid
  • 1,656
  • 3
  • 15
  • 21

5 Answers5

19

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.

Ed Harper
  • 21,127
  • 4
  • 54
  • 80
  • 5
    That really sucks! I noticed that the suggestion on Microsoft Connect was opened in 2008, and it is still active. Well, I voted for it anyway, although my hopes aren't very high. – Sub-Star Feb 09 '11 at 15:57
  • http://www.codeproject.com/Articles/1019661/Powershell-Scripts-to-Replace-setvar-Variable-in-S – Kody Mar 14 '16 at 18:25
  • Seems Connect is now dead, so I've opened a suggestion on the SQL Server forum on Azure Feedback: https://feedback.azure.com/forums/908035-sql-server/suggestions/36971341-change-precedence-of-scripting-variables-in-sqlcmd – Rob Gilliam Feb 27 '19 at 16:19
7

Try editing your sqlproj file and add the following property

<CommentOutSetVarDeclarations>true</CommentOutSetVarDeclarations>

The generated sql file will have the setvars commented out and you can then use the command line to set the actual value.

Gopal Krishnan
  • 968
  • 11
  • 14
  • 1
    This was really helpful, In my CI build I've added `/p:CommentOutSetVarDeclarations=True` to the msbuild command and from there the precedence issue is no issue anymore. – Ibrahim ben Salah Jan 16 '18 at 12:09
7

I was struggling with this as well, but I remember having noticed msdeploy.exe also being able to execute sql scripts with variables. But, for some weird reason, msdeploy.exe is able to pass variables from the command line with the variable values from the command line having precedence over the values defined in the script itself.

An example: I have a sql script (NavDbSecurity.sql) which has three parameters defined:

:setvar loginName "testLoginName"
:setvar databaseName "testDatabaseName"
:setvar NavCompanyName "blablabla"

When I execute the following msdeploy script, the parameter values I pass through the commandline take precedence over the values defined in the script file (don't mind the sa user without password ;)):

msdeploy.exe -verb:sync -source:dbfullsql="c:\NavDbSecurity.sql" -dest:dbfullsql="data source=.\sqlexpress;initial catalog=data base;User Id=sa;Password=;",transacted=False -setParam:kind=SqlCommandVariable,scope="NavDbSecurity.sql",match=databaseName,value="[data base]" -setParam:kind=SqlCommandVariable,scope="NavDbSecurity.sql",match=loginName,value="domain\user" -setParam:kind=SqlCommandVariable,scope="NavDbSecurity.sql",match=NavCompanyName,value="testCompany"
MichaelvR
  • 494
  • 1
  • 5
  • 11
3

Consider using the :r filename command for your setvars.

Being a separate file, you can use tha same filenameacross your deployment regions each containing their own region specific contents.

:r path\sqlConfig.sql
Himanshu
  • 31,810
  • 31
  • 111
  • 133
Wil Mead
  • 31
  • 1
1

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