Search code examples
sql-server-2012windows-server-2012msbuild-4.0dbdeploy

Unable to run the dbdeploy component using msbuild on windows server 2012 where SQL Server 2012 is installed

I am trying to run the dbdeploy component using MSBuild on a Windows server 2012 where SQL Server 2012 is installed.

On running the msbuild script I am getting the following error:

"E:\TestDeployment\Test.proj" (UpgradeDatabase target) (1) ->(dbDeploy target) ->
  E:\TestDeployment\Test.proj(212,5): error : Unexpected System.IO.FileNotFoundException
error executing SQL command: -- BEGINNING TRANSACTION\r
E:\TestDeployment\Test.proj(212,5): error : PRINT 'Beginning transaction'\r
E:\TestDeployment\Test.proj(212,5): error : BEGIN TRANSACTION \r
E:\TestDeployment\Test.proj(212,5): error : GO\r
E:\TestDeployment\Test.proj(212,5): error : \r
E:\TestDeployment\Test.proj(212,5): error : -- Drop Table ChangeLog\r
E:\TestDeployment\Test.proj(212,5): error : Print 'Checking for Table ChangeLog'\r
E:\TestDeployment\Test.proj(212,5): error : IF (NOT EXISTS(SELECT * FROM sys.objects WHER
E [object_id] = OBJECT_ID(N'[dbo].ChangeLog') AND [type]='U'))\r
E:\TestDeployment\Test.proj(212,5): error : BEGIN\r
E:\TestDeployment\Test.proj(212,5): error :         CREATE TABLE dbo.ChangeLog \r
E:\TestDeployment\Test.proj(212,5): error :         (\r
E:\TestDeployment\Test.proj(212,5): error :                 change_number INTEGER NOT NUL
,\r
E:\TestDeployment\Test.proj(212,5): error :                 delta_set VARCHAR(10) NOT NUL
,\r
E:\TestDeployment\Test.proj(212,5): error :                 start_dt DATETIME NOT NULL,\r
E:\TestDeployment\Test.proj(212,5): error :                 complete_dt DATETIME NULL,\r
E:\TestDeployment\Test.proj(212,5): error :                 applied_by VARCHAR(100) NOT N
LL,\r
E:\TestDeployment\Test.proj(212,5): error :                 description VARCHAR(500) NOT
ULL\r
E:\TestDeployment\Test.proj(212,5): error :         )\r
E:\TestDeployment\Test.proj(212,5): error : \r
E:\TestDeployment\Test.proj(212,5): error :         IF @@ERROR<>0 OR @@TRANCOUNT=0 BEGIN
IF @@TRANCOUNT>0 ROLLBACK SET NOEXEC ON END\r
E:\TestDeployment\Test.proj(212,5): error :                 \r
E:\TestDeployment\Test.proj(212,5): error :         ALTER TABLE ChangeLog ADD CONSTRAINT
PK_ChangeLog PRIMARY KEY (change_number, delta_set)\r
E:\TestDeployment\Test.proj(212,5): error :         IF @@ERROR<>0 OR @@TRANCOUNT=0 BEGIN
IF @@TRANCOUNT>0 ROLLBACK SET NOEXEC ON END\r
E:\TestDeployment\Test.proj(212,5): error : END\r
E:\TestDeployment\Test.proj(212,5): error : GO\r
E:\TestDeployment\Test.proj(212,5): error : IF @@ERROR<>0 OR @@TRANCOUNT=0 BEGIN IF @@TRA
NCOUNT>0 ROLLBACK SET NOEXEC ON END\r
E:\TestDeployment\Test.proj(212,5): error : GO\r
E:\TestDeployment\Test.proj(212,5): error : \r
E:\TestDeployment\Test.proj(212,5): error : UPDATE dbo.ChangeLog SET complete_dt = getdat
e() WHERE complete_dt IS NULL\r
E:\TestDeployment\Test.proj(212,5): error : GO\r
E:\TestDeployment\Test.proj(212,5): error : \r
E:\TestDeployment\Test.proj(212,5): error : IF @@ERROR<>0 OR @@TRANCOUNT=0 BEGIN IF @@TRA
NCOUNT>0 ROLLBACK SET NOEXEC ON END\r
E:\TestDeployment\Test.proj(212,5): error : GO\r
E:\TestDeployment\Test.proj(212,5): error : \r
E:\TestDeployment\Test.proj(212,5): error : -- COMMITTING TRANSACTION\r
E:\TestDeployment\Test.proj(212,5): error : IF @@TRANCOUNT>0\r
E:\TestDeployment\Test.proj(212,5): error : BEGIN\r
E:\TestDeployment\Test.proj(212,5): error :         PRINT 'Committing transaction'\r
E:\TestDeployment\Test.proj(212,5): error :         COMMIT TRANSACTION \r
E:\TestDeployment\Test.proj(212,5): error : END\r
E:\TestDeployment\Test.proj(212,5): error : GO\r
E:\TestDeployment\Test.proj(212,5): error : \r
E:\TestDeployment\Test.proj(212,5): error : SET NOEXEC OFF\r
E:\TestDeployment\Test.proj(212,5): error : GO\r
E:\TestDeployment\Test.proj(212,5): error :
E:\TestDeployment\Test.proj(212,5): error : Could not load file or assembly 'Microsoft.SqlServer.BatchParser, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91' or one of its dependencies. The system cannot find the file specified.

The above script is working fine in my Dev box and also on a box (Windows Server 2008 R2) where SQL SERVER 2008 R2 is installed. Issues started once I tried to test it on a new box where SQL SERVER 2012 is installed.

Can anyone help me to resolve this issue?

Solution

  • I analyzed the issue further and found that I need to update the dbDeploy component. I updated the existing project with DbDeploy.Net 2 found at the URL: http://dbdeploynet2.codeplex.com/ and then made changes to the existing msbuild script based on the examples mentioned and it started working fine for me. The only issue I faced after this implementation is usage of DedicatedAdminConnection="True" was giving one more error related to the SQL connectivity. Hence I prefered not to use the DedicatedAdminConnection="True" in the msbuild scripts.