On deploy - overriding a sqlcmd variable

Question

Hello there,

I have a sqlcmd variable that I would like to override with a command line switch at deploy time.  I would like to know the proper way to go about this.  I saw the "Overriding SQLCMD variables" example in BOL and I thought it was a little too complex for what I am trying to do (just overriding a variable at deploy time.)


Here are the steps that I took.

1) Define a SQLCMD variable in the Database.sqlcmdvars file: I defined the variable $LogPath to define where my LogFile exists

2) Added the following code to the dbproj file above the <ItemGroup> node

<Target Name="BeforeDeploy">

<CreateItem Include="LogPath=$(LogPath)">

<Output TaskParameter="Include" ItemName="SqlCommandVariableOverride" />

</CreateItem>

</Target>

When I run msbuild from the command line:

msbuild MyProject.dbproj /t:deploy /p:LogPath="D:\\Log"

I can see the correct value in my output .sql file.

:setvar LogPath "D:\Log"

However, I just did this with playing around, I have no idea if this is the correct way to do it.  Another question would be, with this addition I can no longer deploy the project using Visual Studio only with msbuild, is there any way around this?

thanks,

Creighton

Answer 1

 

Hi Creighton,

 

This is maybe a little more complicated than you need.  If you just want to be able to override the LogPath variable on the commandline, but want it to have a default value (defined in the .sqlcmdvars file) you could define an MSBuild item like this:

 

  <ItemGroup>

    <SqlCommandVariableOverride Include="LogPath=$(LogPath)" Condition="'$(LogPath)' != ''"/>

  </ItemGroup>

 

 

This would only set the LogPath item if the LogPath MSBuild variable was defined on the commandline.  If the variable is not defined then the default in the .sqlcmdvars file would be used.

---

Jamie Laflen, Developer, Microsoft

Answer 2

Thanks that worked great!  Where can I find more documentation for these types of issues related to build and project files?

thanks,

Creighton

Answer 3

Hi Creighton,

 

Well, modifying the project file’s XML a little more challenging because it also involves knowledge of MSBuild.  The MSBuild reference is here:

 

MSBuild Reference

http://msdn.microsoft.com/en-us/library/0k6kkbsd.aspx

 

But there are also some higher-level details in this section of the online documentation:

 

MSBuild Overview

http://msdn.microsoft.com/en-us/library/ms171452.aspx

 

This said, I will see if we can update our online documentation to include this question – it seems like a pretty common thing for someone to want to do and it would be good to have a sample.

---

Jamie Laflen, Developer, Microsoft