SQL Agent Job not sending email failure when 1 step failed

Question

SQL Agent Job not sending email failure when 1 step failed

Dee Dee-422077

Ten Centuries

Points: 1082
More actions
April 24, 2017 at 7:59 am

#329912

Hi,
I have a SQL Server Agent Job which have 5 steps. Each step run a different store procedure. It setup to start at step 1 and keep go to next step even if one of the step failed.
Notification was setup to send email to the group When the job fails
My issue is if step 1 success-> go to Step 2 and it failed -> go to step 3, 4 ,5 and all success. Email did not send.
How can I setup to make it send email if one of the step failed.
Please advise.
Thanks,
Ddee

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Thom A SSC Guru Points: 99126 More actions · Answer 1

If step 1 is set to go to step 3 on failure, then no, it won't email you. The Email notification to operator is on job failure, not task failure. Going to another task on failure avoids job failure.

You would need to either put logic in your tasks to send an email that task 1 failure, or set task 1 to cause the job to fail (and task 3 onwards would not run).

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

Dee Dee-422077 Ten Centuries Points: 1082 More actions · Answer 2

Hi Thom,
Sorry to confuse you but the job set up as:
step 1 on success go to next step; On failure Go to next step
step 2 on success go to next step; On failure Go to next step
step 3 on success go to next step; On failure Go to next step
step 4 on success go to next step; On failure Go to next step
step 4 on success Quit the job reporting success ; On failure: Quit the job reporting failure

When the job trigger, step 1 is success, step 2 failed, step 3,4 and 5 are all success. and email did not send.
How can I make sure email will send if any of the step is failed?

if I select Notification to Send email "When the job completes" -> would email will send in the scenario I have above?
or
if I select Notification to Send email "When the job succeed" -> would email will send in the scenario I have above or not at all because one step is failed?

Thanks,

Ddee

Thom A SSC Guru Points: 99126 More actions · Answer 3

You would need each task (apart from the last) to be:
On Success go to next step; On failure quit the job reporting failure.

This will cause the job to fail and an email to be sent to the notification operator. As i outlined, whoever, the job will stop at the failed task.Otherwise, you will need to put logic in each of your tasks (the SQL, SSIS package, whatever they are) that handles the error and sends an email (for example, via sp_send_dbmail) when an error occurs.

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

Dee Dee-422077 Ten Centuries Points: 1082 More actions · Answer 4

Hi Thom,
Thanks for your reply... but I need to have other steps to run if step 2 failed.

Can you please advise me with the question below:

if I select Notification to Send email "When the job completes" -> would email will send in the scenario I have above?
or
if I select Notification to Send email "When the job succeed" -> would email will send in the scenario I have above or not at all because one step is failed?
Thanks,

Ddee

Thom A SSC Guru Points: 99126 More actions · Answer 5

Send email "When the job completes" Will send an email when the job completes, regardless of if it was successful or failed. If the job fails it will tell you the reason, however, if a step fails and the job succeeds, it will not tell you.
Send email "When the job succeed" will send an email when the job succeeds. If it fails, not email will be sent.

You seem to be confusing steps and jobs still though, so I'll try and explain a little further:
A SQL Agent job completes one or more tasks. Each step will be run in the order you state (in your example, Step 1, step 2, step 3, step 4, step 5). Each step has a setting for what happens after it succeeds, and after it fails. It is important to note, at this point, that a step failing does not mean that a job will be reported as failing. The fail a Job you must declare the step to report failure, this can either be in the success action or the failure action.

At the moment, you have your tasks set to run the next step on failure, so I'll explain how this works for your scenario with step 2 failing.

Job starts. Job success/Failure status is unknown.
Step 1 runs. Job Success/Failure status is unknown.
Step 1 succeeds, step logic states to advance to next step (step 2) on success. Job success/Failure status is unknown.
Step 2 runs. Job success/Failure status is unknown.
Step 2 fails. step logic states to advance to next step (step 3) on failure. Job success/Failure status is unknown.
Step 3 runs. Job success/Failure status is unknown.
Step 3 succeeds. step logic states to advance to next step (step 4) on success. Job success/Failure status is unknown.
Step 4 runs. Job success/Failure status is unknown.
Step 4 succeeds. step logic states to advance to next step (step 5) on success. Job success/Failure status is unknown.
Step 5 runs. Job success/Failure status is unknown.
Step 5 succeeds. step logic states to quit the job reporting success on success. Job success/Failure status is known (success)
Job quits, reporting success.
Operator is NOT emailed, as this only occurs on job failure.

What you want to do on your steps is quit and report failure on failure. So, again, using your logic, the following would occur:

Job starts. Job success/Failure status is unknown.
Step 1 runs. Job Success/Failure status is unknown.
Step 1 succeeds, step logic states to advance to next step (step 2) on success. Job success/Failure status is unknown.
Step 2 runs. Job success/Failure status is unknown.
Step 2 fails. step logic states to quit the job reporting failure on failure. Job Success/Failure status is known (Failure)
Job quits (NO MORE STEPS ARE RUN), reporting failure.
Operator is emailed, advising that the job failed, on step 2, and the reason for the failure.

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk