Assigning MSBuild Properties to SQLCMD Variables

I am receiving the following question a lot: How do I propagate MSBuild properties to SQLCMD variables. In order to make the Visual Studio 2008 Team System Database Edition GDR release use the data from an MSBuild property inside a SQLCMD variable you need to do three things:

  1. Add the variable to the Database.sqlcmdvars file
  2. Add a XML fragment to the database project (.dbproj) file
  3. Use the SQLCMD variable

First you need to add a SQLCMD variable to the Database.sqlcmdvars file, in this example we will add the $(ProjectDirectory) variable, which we want to use the reflect the MSBuild $(MSBuildProjectDirectory) property. Set the value to something that you can recognize as not being set, you cannot leave it empty, so I will use “notset”.


Now that we have set the variable, we need to add some information to the database project file to map the value from the MSBuild property to the SQLCMD variable we just defined.

In order to achieve this we need to add an ItemGroup to the project file. There is no UI support for doing this, so you manually need to edit the database project file. There are multiple ways of doing this, I will describe one, which assumes you have the project loaded inside Visual Studio as a starting point.

  1. Right click on the database project node inside the Visual Studio Solution Explorer, this will show the following menu, where you choose the “Unload Project” option
  2. Now the project is unloaded and the Solution Explorer windows only shows the project node as “(unavailable)”.
  3. Now right click on the project node again and choose the “Edit <project name>.dbproj” options
  4. Go to the end of the project file and add the following ItemGroup before the end of the </Project> element
       1:    <ItemGroup>
       2:      <SqlCommandVariableOverride Include="ProjectDirectory=$(MSBuildProjectDirectory)" />
       3:    </ItemGroup>

  5. The ItemGroup defines an element of type SqlCommandVariableOverride and contains an attribute which is a string that has on the left hand side of the equation the SQLCMD variable name and on the right hand side the MSBuild property name. Now you have mapped the MSBuild property to the SQLCMD variable
  6. Right click on the database node inside Solution Explorer again, now choose the “Reload Project” option. This will close the project file inside the XML editor and open the project again. If you did not close the editor, Visual Studio will tell you this action will close the project file inside the editor.

  7. Now we can use the variable, and to test we first add the following statement to the Script.PreDeplomenent.sql file.

       1:  print N'SQLCMD ProjectDirectory = $(ProjectDirectory)'

  8. To test it we need to deploy the project, to the database, not just to script, otherwise the pre-deployment script will not get executed. The result should like something like this inside the Output window:


Now that we know the variable assignment works, we can use the variable in other more interesting places.


In the next post I will cover how to make the variable assignment conditional.

GertD @

Comments (7)

  1. Brandon says:

    I am getting the error:

    the element itemgroup in namespace has invalid child element sqlcommand variable override

    and the variable is not being passed.

    This is what I have.


       <SqlCommandVariableOverride Include="AssemblyVersionNumber=$(BuildNumber)" />


    There is a blue squiggly under SqlCommandVariableOverride

  2. Q: I am getting the error: the element itemgroup in namespace has invalid child element sqlcommand variable override

    A: The red squiggly is expect, since the schema for the XLM ( does not include the definitions, which is not a problem

    You will see multiple warnings when opening the XML of the project file in the error list like this:

    The element ‘ItemGroup’ in namespace ‘‘ has invalid child element ‘SqlCommandVariableOverride’ in namespace ‘‘. List of possible elements expected: ‘Item’ in namespace ‘‘.

    Q: variable is not being passed

    A: Did you define the BuildNumber property?

    I created a sample project, available at:

    which outputs:

       Creating SqlCmdMSBuild…

       SQLCMD ProjectDirectory = d:demoSqlCmdMSBuild

       SQLCMD AssemblyVersionNumber =

    But you have to define the property BuildNumber like this:




  3. Alfred says:

    Thanks for this post! Invaluable info…

  4. Wes says:

    This works for basic variables, but I tried to override a connection string, and it seems to fail on the fact that a connection string contains multiple equal ('=') signs in the string:


       <SqlCommandVariableOverride Include="TargetConnectionString=Data Source=devdb;Integrated Security=True;Asynchronous Processing=True;" />


    D:…DB.dbproj" (deploy target) (1) -> (DspDeploy target) ->

     DB.dbschema : Deploy error TSD01267: The SqlCommand variable Integrated Security was not defined in a sql command variables file so it cannot be overridden.

    It goes on to give the same error for "Asynchronous Processing" and "Connect Timeout"

    Is there a way to override a connection string or am I stuck?

  5. Dewang says:

    Thanks for that, really useful.

    One question – how do I make it cope with the variable value being an empty string?

    I'm using VS 2010 against SQL 2008 and it complains saying the override must be in the format name=value. Which it is, except that value is an empty string!

  6. Hi,

    I tried this on VStudio 2010 for databases without success. When I check what the PRINT statements returns, it was still “notset”, no overwrite occurred.

    Can that be adapted for VStudio 2010/2012?