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

Question

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?

Answer 1

Check your environment variables between the machine that does work and the one that doesn't. This assumes that you are opening a Command Prompt and then running the MSBuild.

I have SQL Server 2012 installed on my machine and found BatchParser.DLL in:

• C:\Program Files (x86)\Microsoft SQL Server\110\DTS\Binn
• C:\Program Files (x86)\Microsoft SQL Server\110\Tools\Binn
• C:\Program Files (x86)\Microsoft SQL Server\Client SDK\ODBC\110\Tools\Binn

So, the most likely cause of the error is that you are missing an environment variable altogether that would point to one of these.

As far as I can see, they are only in my PATH variable.

If you cannot find this DDL in any of those locations, then you need to install SMO as discussed in these S.O. questions:

• Can't find microsoft.sqlserver.batchparser.dll
• Where can i find "Microsoft.SqlServer.BatchParser.dll"?

EDIT:
The final thing to check is if there is also a C:\Program Files (x86)\Microsoft SQL Server\100 folder that would then have the DTS\Binn and/or Tools\Binn folders in it. My guess is that, based on the error being

Could not load file or assembly 'Microsoft.SqlServer.BatchParser, Version=10.0.0.0

that it is specifically looking for the 2008 version of SMO, something that your dev box has but is not on the new box that has SQL 2012 only. Your options are then:

• Try simply copying "batchparser.dll" from your dev box over to the new SQL 2012 server and place it in the C:\Program Files (x86)\Microsoft SQL Server\110\Tools\Binn folder.
• Download and install the SQL Server 2008 R2 Feature Pack, which includes SMO, on the new SQL Server 2012 server. Expand the "Install Instructions" section and go to "Microsoft® SQL Server® 2008 R2 Shared Management Objects" to grab the proper MSI.

Answer 2

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.