I had a situation recently where I was building a solution that contained a Database project (.dbproj) built using Visual Studio 2005 Team Edition for Database Professionals (aka DataDude).

In Team Build there were two different build types.  One for Continuous Integration and one for the Nightly Build.  The CI build simply compiled the solution and ran some tests locally, whereas the nightly build also deployed the database to a test server and refreshed the application on the test server.

As part of the build process I had something like this in the project file:

<PropertyGroup>
  <TargetConnection_A>DataSource=ServerA%3BIntegrated Security=True%3BPooling=False%3B</TargetConnection_A>
  <TargetConnection_B>DataSource=ServerB%3BIntegrated Security=True%3BPooling=False%3B</TargetConnection_B>
</PropertyGroup>
<Target Name="xxx">
  <Message Text="Deploying Database Locally" />
  <MSBuild Projects="db1.dbproj" Targets="Build;Deploy" Properties="TargetConnectionString=$(TargetConnection_A)" />
  <Message Text="Deploying Database Locally" />
  <MSBuild Projects="db1.dbproj" Targets="Deploy" Properties="TargetConnectionString=$(TargetConnection_B)" />
</Target>


Simple enough, really.  To do the second deployment you just change the connection string for the database project and re-deploy, right?  Well, actually it's not.  If you try this you'll get an error in your build log that looks something like this:


Deploy error TSD151: .Net SqlClient Data Provider: Msg 50000, Level 16, State 127, Line xx The server name in the build script ServerB does not match the name of the target server ServerA.  Verify whether your database project settings are correct and whether your build script is up to date.


This is a bit surprising, especially as the build & deploy to Server A works correctly.


Well, as it turns out, when a database project is built it creates one huge uber-script and embeds within it the knowledge of the server it should be deployed to.  It also adds a check that the server in the connection string matches the server it thinks it should be deploying to, so even though you change the connection string before calling the script you'll get an error occurring based on the fact that the servers don't match.


If you look at the MSDN documentation on this topic they'll tell you to modify the database project settings directly in order for the deploy to work in Team Build, however this is a flawed approach as changing the project properties will affect everyone in the team (and you don't want them all deploying to the one database by default, do you?) and it also doesn't solve the problem of deploying the database twice.


Now you could try changing the targets in the second MSBuild step to use "Clean;Build;Deploy" as the targets but what happens in this case is that the compiler determines that the source hasn't changed and it therefore has nothing to do, and the uber-script isn't changed.  As such you'll still received the same error as before.


The solution then is to force a rebuild.  This is easily done by changing the targets for the second MSBuild to use "Rebuild;Deploy" as shown here.


<MSBuild Projects="db1.dbproj" Targets="Rebuild;Deploy" Properties="TargetConnectionString=$(TargetConnection_B)" />


Once you've done that, everything should work and your builds will then deploy the database to multiple servers as expected.