DATABASE PROJECT SQLCMD -v

Question

To control the database version, I use the Visual Studio database project but to execute the script generated by the project I use the sqlcmd.exe.

There is a variable called $(environment) in my script.postdeployment.sql that depends on the server environment (prd,uat,dev) and I use the command sqlcmd.exe -v environment="dev" to set this value to this variable. The problem is the Visual Studio throw a compilation error

SQL72008: Variable Environment is not defined.

Ok, I know why this error is trown, It is because the variable is not defined in the Prject properties (SQLCMD Variables tab), BUT if I add this variable in the project properties the value will be setted in the script overriding the value I passed in the sqlcmd.exe -v environment="dev".

For example

, If I set the $(environment) variable in the project properties as "uat" and execute sqlcmd.exe -v environment="dev", the highest precedence is the value I set in the project properties, so UAT, because the project generate the following statment in my script:

:setvar app_environment "uat"

How could I handle this issue between visual studio database project and sqlcmd.exe?

I Would like that Visual Studio avoid the statment generation ":setvar app_environment "uat"", but only for this variable... It would resolve my problem... — Aitiow
– Aitiow, Commented Feb 3, 2015 at 13:55
Have you tried using a Publish Profile to set the variable, then calling that from the command line? schottsql.blogspot.com/2012/11/… — Peter Schott
– Peter Schott, Commented Feb 3, 2015 at 14:21
Yeah, Actually I do this, buy. If a create a variable in the publish profile named as Environment, it would be necessary to create multiples files for each environment and repeat values that don't change by environment. It is not good!! — Aitiow
– Aitiow, Commented Feb 3, 2015 at 14:52

Jon · Accepted Answer · 2017-01-14 00:43:34Z

1

The way I have fixed this is to Set the properties of the post deployment script as follows:

Build Action: None

Copy to Output Directory: Copy always

When you do this, Visual Studio won't build the post script file, but will copy it to the /bin folder. You can then call SQLCMD twice. Once for the database creation script, and then a second time for the post build script using the -v option.

edited Jan 14, 2017 at 0:43

answered Jan 14, 2017 at 0:38

Jon

113 bronze badges

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

Collectives™ on Stack Overflow

DATABASE PROJECT SQLCMD -v

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related