SQL Scripts Being Executed Do Not Return Error Message On Failure

I have a project that consists of various Database Change Scripts for testing (SQL Server provider). I have purposefully set up a script that alters a table that doesn't exist (should fail/produce an error message). However, when I run this script through Build Master for a deployment to an environment it produces a success and no issue is raised.

Can you let me know if there is any configuration that can be set to allow SQL Scripts that have errors to be shown as errors instead of successful? It seems like as long as script can be sent to remote agent it is considered successful.

Product: BuildMaster
Version: 4.1.5

Those are supposed to be reported as errors by default, i.e. there is no configurable options for that behavior. What version of the SQL Server extension are you using, and which action specifically is causing this?

Also, if it's possible, what happens if you run this script from the BuildMaster server instead of a remote agent? It's pretty rare that agents are required to run SQL scripts. That's generally only a requirement if the database is only accessible locally.

Please advise if we are setting something up wrong as we are evaluating the software. Since I am a DBA I set up a "dummy" release that just has connections to database server and deploying database change scripts in order to understand how deployments on the SQL side of things will work.

We installed a BuildMaster TCP agent on our Integration database server running under local system for testing. The SQL Server has named instance on that server where the AdventureWorks2008 database resides.

This is the SQL text for 777_FailAlter script. It is a simple script against an AdventureWorks2008 database for testing. Note that table "TableName" doesn't exist so I would expect a failure (as it fails when being ran in Management Studio).

ALTER TABLE [dbo].[TableName] ADD [ColumnName] INT NOT NULL DEFAULT(0)
GO

This is from the execution log for the release (ServerName is X'd out)...

Action Group 64: Deploy Database ScriptsAction 1: Execute database change scripts against Integration AdventureWorks SQL Server.Custom Variables: (none)Source and Target directory same; splitting.AgentBasedAction Initialization Complete: Temp Directory: C:\Program Files\BuildMasterAgent\_AGTTMP\_A4\_S0\TMP Source Directory: C:\Program Files\BuildMasterAgent\_AGTTMP\_A4\_S0\SRC Target Directory: C:\Program Files\BuildMasterAgent\_AGTTMP\_A4\_S0\WRK Server: XXXXXXXXXXXX (Id: 2)Initializing action...Initialization complete. Executing...DB Version: 1Current Version: 1Total Scripts in BuildMaster: 5Scripts Reported Run by Target DB: 7Determining scripts that need to be run...Scripts to run: 1Executing 777_FailAlterThe script "777_FailAlter" executed successfully.Execution Complete.Execution complete. Finalizing...Target directory unused; joining with source.Finalization complete.

Below is from the __BuildMaster_DbSchemaChanges on the Integration SQL Server. So we know that BuildMaster is recording the script as executing successfully in the table and not failing it (sorry for bad formatting).

Numeric_Release_Number Script_Id Script_Sequence Batch_Name Executed_Date Success_Indicator
1 7 1 777_FailAlter 3/31/14 1:16 PM Y

The following is a list of all the core assemblies and extension assemblies that are loaded on the agent.

Loaded Extensions
DotNetRecipes	4.3.2.0
SqlServer	4.1.1.0
TFS2012	4.4.6.0
Tutorials	4.1.2.0
Windows	4.5.1.0
WindowsSdk	4.8.1.0
Core Assembly Versions
BuildMaster	4.1.5.8
BuildMaster.Extensibility	4.1.5.8
BuildMaster.Web.Controls	4.1.5.8
BuildMasterCore	4.1.5.8
BuildMasterExtensions	4.1.5.8
ICSharpCode.SharpZipLib	0.86.0.518
InedoLib	82.1.0.0
Menees.DiffUtils	2.0.2.0
Renci.SshNet	2013.4.7.0

Thank you for the very detailed information. We discovered the issue was actually related to the SQL Server extension version shipped with the BuildMaster v4.1.5 installer (SQL Server extension v4.1.1) when the actual released extension was v4.1.3. This is what we get for forcing builds (BuildMaster does not usually allow promotions of dependency applications if the dependent is not released.)

While downloading v4.1.3 of the extension would fix it, we have also just released version v4.2.9 of the extension that includes the same fix.

Wanted to follow up on this issue and thank you for the response. We upgraded from 4.1.5 to 4.1.6 and I reran a script that should have failed and it did indeed throw an error. SQL Server Extension was upgraded to 4.1.3.0 and the expected error is thrown. Thanks again.

Source and Target directory same; splitting. AgentBasedAction Initialization Complete: Temp Directory: C:\Program Files\BuildMasterAgent_AGTTMP_A4_S0\TMP Source Directory: C:\Program Files\BuildMasterAgent_AGTTMP_A4_S0\SRC Target Directory: C:\Program Files\BuildMasterAgent_AGTTMP_A4_S0\WRK Server: XXXXXXXXXXXXX (Id: 2) Initializing action... Initialization complete. Executing... DB Version: 1 Current Version: 1 Total Scripts in BuildMaster: 8 Scripts Reported Run by Target DB: 10 Determining scripts that need to be run... Scripts to run: 1 Executing FailFailFail The script "FailFailFail" execution failed. SQL Error: Cannot find the object "dbo.KSJHFKSHFKJSSKFLSJKDF" because it does not exist or you do not have permissions.

Execution Complete. Execution complete. Finalizing... Target directory unused; joining with source. Finalization complete.