How can I get an error message that SQL will see as a job failure?

Question

I have a task scheduled using an SQL Server agent that runs sproc, which runs some other sprocs. Each sproc looks like this:

BEGIN TRY -- do stuff END TRY BEGIN CATCH DECLARE @errorMessage varchar(4000) DECLARE @procName varchar(255) SELECT @errorMessage = error_message() SELECT @procName = OBJECT_NAME(@@PROCID) RAISERROR('%s threw an exception: %s', 16, 1, @procName, @errorMessage) END CATCH 

All this works great - errors go up and stack, life is good. However, my RAISERROR calls do not crash the job — I am set to receive a notification when the job doesn’t work, but never receive it. Email notifications work because I receive emails if I change the notification to "when the job is successful." Is there any other function that I should use here instead of RAISERROR?

Answer 1

Raise the error in the try block with a severity between 11-19 in the TRY block and then re-set the same error in the catch block. This will make the step unsuccessful.

code snippet from msdn

 BEGIN TRY -- RAISERROR with severity 11-19 will cause execution to -- jump to the CATCH block. RAISERROR ('Error raised in TRY block.', -- Message text. 16, -- Severity. 1 -- State. ); END TRY BEGIN CATCH DECLARE @ErrorMessage NVARCHAR(4000); DECLARE @ErrorSeverity INT; DECLARE @ErrorState INT; SELECT @ErrorMessage = ERROR_MESSAGE(), @ErrorSeverity = ERROR_SEVERITY(), @ErrorState = ERROR_STATE(); -- Use RAISERROR inside the CATCH block to return error -- information about the original error that caused -- execution to jump to the CATCH block. RAISERROR (@ErrorMessage, -- Message text. @ErrorSeverity, -- Severity. @ErrorState -- State. ); END CATCH; 

Answer 2

At each step of the task, there is an action for the failure of the step; they must establish the rejection of work. You are probably set to the next step.

Answer 3

If the severity level of the error is 20 or higher, the connection to the database will be terminated. This will cause the step to fail. As long as your step is set so that the operation will fail, if the step fails, you will get the desired result.

 RAISERROR('%s threw an exception: %s', 20, 1, @procName, @errorMessage) WITH LOG; 

SEE msdn description RAISERROR: https://docs.microsoft.com/en-us/sql/t-sql/language-elements/raiserror-transact-sql