locked
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



    Monday, January 05, 2009 7:39 PM

Answers

  •  

    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
    • Marked as answer by CR8N Tuesday, January 06, 2009 5:05 AM
    Tuesday, January 06, 2009 2:35 AM

All replies

  •  

    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
    • Marked as answer by CR8N Tuesday, January 06, 2009 5:05 AM
    Tuesday, January 06, 2009 2:35 AM
  • Thanks that worked great!  Where can I find more documentation for these types of issues related to build and project files?

    thanks,

    Creighton
    Tuesday, January 06, 2009 5:05 AM
  • 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
    Wednesday, January 07, 2009 2:58 AM
  • This works for me when i try to set the $(BuildID), unfortunately if I try to set this variable when I build from Visual Studio, it bombs because it is null on MsBuild.

    Using this trick it works perfectly!

    Thank you

    Wednesday, January 11, 2012 6:38 PM