0

Is there a way to make a scheduled job terminate (stop, quit, stop executing, report failure) within a stored procedure in that job?

For example, I have some check queries within a TRY block that do RAISERROR (59834,16,1) to go to the CATCH block to send an email saying that the check queries found a bad situation and the job must stop. The number 59834 was arbitrary but specific. However, every time I test the job, even though I raised an error, the job continues to execute; it never reports a failure.

I have tried using the same RAISERROR in the CATCH block as I did in the TRY block, but that did not work either.

I want the job to stop in this once instance, it will run every day.

Chythe
  • 23
  • 6

1 Answers1

0

It sounds like the XACT_ABORT setting is not on. Without XACT_ABORT on, the execution will continue regardless of any errors encountered. More information about XACT_ABORT:

Microsoft BOL entry for XACT_ABORT

What is the benefit of using SET XACT_ABORT ON in a stored procedure?

So you might consider a SET XACT_ABORT ON at the start of the job.

Community
  • 1
  • 1
Paul Williams
  • 16,585
  • 5
  • 47
  • 82