MSBuild + .dbproj

Question

i am trying to deploy db project through msbuild.

i am getting below error

MSBuild - Deploy DB project
C:\Windows\Microsoft.NET\Framework\v4.0.30319\MSBuild.exe
    /nologo
    /noconsolelogger "C:\Builds\2\Test\ePlanner\Sources\WebSiteBuildTest\Database\ePlanner\ePlanner\ePlanner.dbproj"
    /m:1
    /t:"Deploy"
    /fl
    /flp:"logfile=deploymentdb.log;encoding=Unicode;verbosity=normal"
    /p:TargetDatabase="ePlanner3";"TargetConnectionString="Data Source=SACHIN-PC%3Buser=sa%3Bpwd=M0nday!";"DefaultDataPath="C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA";DeployToDatabase=true
    /p:OutDir="C:\Builds\2\Test\ePlanner\Binaries\\"
    /p:Configuration="Debug"
    /p:RunCodeAnalysis="False"
    /dl:WorkflowCentralLogger,"C:\Program Files\Microsoft Team Foundation Server 2010\Tools\Microsoft.TeamFoundation.Build.Server.Logger.dll";"Verbosity=Normal;BuildUri=vstfs:///Build/Build/53;InformationNodeId=10631;TFSUrl=http://sachin-pc:8080/tfs/DefaultCollection;"*WorkflowForwardingLogger,"C:\Program Files\Microsoft Team Foundation Server 2010\Tools\Microsoft.TeamFoundation.Build.Server.Logger.dll";"Verbosity=Normal;"
MSBUILD : error MSB1008: Only one project can be specified.
    Switch: Source=SACHIN-PC%3Buser=sa%3Bpwd=M0nday!;
    DefaultDataPath=C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA;DeployToDatabase=true
    For switch syntax, type "MSBuild /help" 
    TF270015: 'MSBuild.exe' returned an unexpected exit code. Expected '0'; actual '1'.

Please help me out

Answer 1

In order to run a .dbproj in MsBuild the machine needs to have VisualStudio with the database project components installed, something to do with licensing. I cannot tell from the error dump that you provided if this is what is causing you the issue but it solved our issue when building on our build server.

Answer 2

You've got conflicting quotation marks in your command line:

/p:TargetDatabase="ePlanner3";"TargetConnectionString="Data Source=SACHIN-PC%3Buser=sa%3Bpwd=M0nday!";"DefaultDataPath="C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA";DeployToDatabase=true

The TargetConnectionString property is completely surrounded in double quotes, but then you use double quotes to specify the connection string. Try escaping the quotes for the connection string.

Answer 3

Consider converting the project to .sqlproj. This new format provides a "Publish" MSBuild target. You just need to specify at SqlPublishProfilePath property a publish profile file with your deployment options.

Look that sample:

<Project xmlns="http://schemas.microsoft.com/developer/msbuild/2003" DefaultTargets="ProjectsBuild">
  <ItemGroup>
    <!-- Defines a collection of projects to deploy -->
    <ProjectsToPublish Include="SQLServer.sqlproj">
      <Properties>SqlPublishProfilePath=myprofile.publish.xml</Properties>
    </ProjectsToPublish>
    <!-- Runs the target Publish -->
    <MSBuild Projects="@(ProjectsToPublish)" Targets="Publish"/>
  </ItemGroup>
</Project>

Visual Studio 2010/2012 needs the SSDT (Sql Server Data Tools - ) installed to understand .sqlproj. Your build server too. It will install the MSBuild .targets extensions that contains the target "Publish".

Publish profile example (it's a MSBuild project):

<?xml version="1.0" encoding="utf-8"?>
<Project ToolsVersion="4.0" xmlns="http://schemas.microsoft.com/developer/msbuild/2003">
  <PropertyGroup>
    <CreateNewDatabase>True</CreateNewDatabase>
    <TargetDatabaseName>DB1</TargetDatabaseName>
    <TargetConnectionString>Data Source=server1\dev;Integrated Security=True;Pooling=False</TargetConnectionString>
    <BackupDatabaseBeforeChanges>False</BackupDatabaseBeforeChanges>
    <BlockOnPossibleDataLoss>False</BlockOnPossibleDataLoss>
    <NoAlterStatementsToChangeCLRTypes>False</NoAlterStatementsToChangeCLRTypes>
    <DropObjectsNotInSource>True</DropObjectsNotInSource>
    <DeployDatabaseInSingleUserMode>False</DeployDatabaseInSingleUserMode>
    <DeployScriptFileName>db1.sql</DeployScriptFileName>
    <ProfileVersionNumber>1</ProfileVersionNumber>
  </PropertyGroup>
</Project>