Sql-server – SQL Server Agent Notification Email if a Step has an error, but the job is succesfull

sql serversql-server-agent

Is it possible, that I can receive Email from Server Agent, if a step in an Agent has an error, but the Job is successful? In the history, I can see, that for example one Step has an Error, but all others were successful.

Best Answer

Yes you can achieve this with help of SP:

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

You can refer here how it is being used.

Adding@ You can recieve email if Database mail is configured in very first place. On how to set that up read How to Set Up Database Mail for SQL Server Job Failures

Also, In SQL Agent Job Properties-> step > edit> Advanced --you can also check Include step output in history to get the message from step failure.

Leave the On failure action to Quit the job reporting failure.

Related Question