2

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.

1
  • it was not possible to solve the Problem. If an error exists, the Job is ending. i want, that the Job runs all steps, for example from step 1 still step 6. If Step 3 has an error, i want have an email, that step 3 failed, but the whole Job must run succesfully.
    – sengul
    Jul 24, 2015 at 13:20

1 Answer 1

1

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) = '[email protected]';
        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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.