Error in Azure DevOps SQL Server database deploy task not failing pipeline

Question

I'm using Azure DevOps SQL Server database deploy task to run a SQL Server Agent Job using its inline SQL feature which works.

However if the job fails I have some logic that generates an error from the SQL script as per below. When the script errors, that error correctly gets displayed in the task logs, however it doesn't fail the whole task but rather returns a succeeded status. This confuses the developer as they think their changes are not breaking the job when in fact they are.

Is there anyway to bubble up the error generated form the SQL script so that the SQL Server database deploy task returns a failed status instead of a succeeded status.

Please see screen shots and YAML of the release issue below:

steps:
- task: SqlDacpacDeploymentOnMachineGroup@0
  displayName: 'Run SQL Agent Job'
  inputs:
    TaskType: sqlInline
    InlineSql: |
     /* SQL Agent Job Logic Here... */
     
     /* Raise error if job fails */
     
     RAISERROR('##vso[task.logissue type=error]]%s job has either failed or is not in a good state!', 16, 1, 'JobName') WITH LOG
    DatabaseName: master
    AdditionalArgumentsSql: '-QueryTimeout 1800 -IncludeSqlUserErrors -OutputSqlErrors 1 -Verbose'

According to Azure DevOps, this step is deprecated. Maybe it's just not functioning correctly because of changes in how the DevOps pipeline works? Guessing a bit here. Based on overall function, you should see an error. — Grant Fritchey
– Grant Fritchey, Commented Jul 23, 2020 at 11:54
Do you have a link saying it has been deprecated? Its not obvious looking at the documentation or when I search for SQL Deploy in the Azure DevOps Release Pipeline Task GUI. Do you know if there is a replacement release task I should be using instead? — jeremyh
– jeremyh, Commented Jul 23, 2020 at 14:23
When I looked it up in order to try to help, it was only available on the deprecation list. — Grant Fritchey
– Grant Fritchey, Commented Jul 24, 2020 at 11:15
As to what to use instead, I'd probably just go for a powershell command myself. — Grant Fritchey
– Grant Fritchey, Commented Jul 24, 2020 at 11:16

Leo Liu · Accepted Answer · 2020-07-24 06:22:47Z

1

Error in Azure DevOps SQL Server database deploy task not failing pipeline

You could try to use the Logging Commands to fail build from your Inline Sql scripts:

##vso[task.complete result=Failed;]DONE

##vso[task.complete]current operation

result=Succeeded|SucceededWithIssues|Failed

Finish timeline record for current task, set task result and current operation. When result not provided, set result to succeeded. Example: ##vso[task.complete result=Succeeded;]DONE

answered Jul 24, 2020 at 6:22

Leo Liu

77.8k11 gold badges133 silver badges159 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

jeremyh Over a year ago

I tried this using RAISERROR('##vso[task.complete result=Succeeded;]DONE', 1, 1) and PRINT('##vso[task.complete result=Succeeded;]DONE') at the end of the inline sql script but get the error below. This happens with both a Failed and Succeeded result. 2020-07-24T10:47:51.1356154Z ##[error]The pipeline has been stopped. 2020-07-24T10:47:51.1368181Z ##[error]Object reference not set to an instance of an object.

Collectives™ on Stack Overflow

Error in Azure DevOps SQL Server database deploy task not failing pipeline

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related