Nested SQL in MSBuild not failing TeamCity build

Question

I'm running using the MSBuild build runner in TeamCity 9 as part of my build process.

In some of the MSBuild files, I'm running nested SQL via the MSBuild.ExtensionPack.SqlServer namespace. If an error occurs when running the SQL code, the MSBuild target does not return an error to the TeamCity build, so the overall build status is successful with no errors logged.

How exactly do I get an error to return from SQL to MSBuild in order to fail the TeamCity build that's running it?

Here is sample code with an intentional mistake in the SQL code, this code will not return an error to the build when run:

<Target Name="RunSQL">
    <MSBuild.ExtensionPack.SqlServer.SqlCmd
        TaskAction="Execute"
        CommandLineQuery="EXEC [Database1].dbo].[UpdateSomething]" />
</Target>

ShaneC ShaneC · Accepted Answer · 2016-10-06T15:01:39

I have found the solution in the SeverityLevel property in the SqlCmd class

Setting this SeverityLevel property to 1 sends all errors to the MSBuild task which ultimately fails the TeamCity build that is running it.

My final code looks like this

<Target Name="RunSQL">
    <MSBuild.ExtensionPack.SqlServer.SqlCmd
    TaskAction="Execute"
    SeverityLevel="1"
    CommandLineQuery="EXEC [Database1].dbo].[UpdateSomething]" />
</Target>

Solution obtained from: http://www.msbuildextensionpack.com/help/4.0.9.0/html/3b72c130-7fc9-8b8a-132c-62999e5b1183.htm

Nested SQL in MSBuild not failing TeamCity build

1 Answers