SQL Server 2008: How to send an email when a step in a SQL Server agent job fails, but overall job succeeds I have a SQL Server job with 6 steps.  Steps 5 and 6 must be run regardless of any failures in the first four steps, so these first four jobs are set to skip to step 5 if they fail.

However, if steps 5 and 6 then succeed, the whole job is regarded as a success. I have an email notification set up for the job failure, but I do not receive an email if any of the first four steps fail due to the overall job being considered a success. I would like this to happen.

It would not be ideal to split out the first four steps into a separate job, as they must be completed before steps 5 and 6 begin.

Please can anyone give me advice to solve this problem so that:


Steps 5 and 6 run even when any of steps 1-4 fail.
Steps 5 and 6 begin strictly not before steps 1-4 complete.
When any of steps 1-4 fail, an email notification is sent indicating the step that failed.


Thanks very much in advance for your help.
You could create a Stored Proc for steps 1-4, with error handling that notifies you via email on failure.  

Something like:

CREATE PROCEDURE TestEmailOnFail
AS
BEGIN
    BEGIN TRY
        /*
            Perform some action that might fail
        */
        SELECT 0/0; --THIS WILL FAIL
    END TRY
    BEGIN CATCH
        DECLARE @subject nvarchar(max) = 'Job Failure Notification';
        DECLARE @body nvarchar(max) = 'TestEmailOnFail Job Failed' 
            + CHAR(10) + CHAR(13) + 'Error Number:  ' + CAST(ERROR_NUMBER() AS nvarchar(max))
            + CHAR(10) + CHAR(13) + 'Error Message: ' + ERROR_MESSAGE();
        DECLARE @to nvarchar(max) = 'somebody@email.com';
        DECLARE @profile_name sysname = 'SQLMailProfileName';
        EXEC msdb.dbo.sp_send_dbmail @profile_name = @profile_name,
            @recipients = @to, @subject = @subject, @body = @body;
    END CATCH
END


I just tested this using my SQL Mail Profile, and my email address and got the following email:

TestEmailOnFail Job Failed
Error Number:  8134
Error Message: Divide by zero error encountered.