22

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.

Improve this question

5 Answers 5

Reset to default
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.

Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

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.
codeproject.com/Articles/1019661/…
Seems Connect is now dead, so I've opened a suggestion on the SQL Server forum on Azure Feedback: feedback.azure.com/forums/908035-sql-server/suggestions/…
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"
Improve this answer

Comments

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.

Improve this answer

1 Comment

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.
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
Improve this answer

Comments

1

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

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.