Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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:

  1. Steps 5 and 6 run even when any of steps 1-4 fail.
  2. Steps 5 and 6 begin strictly not before steps 1-4 complete.
  3. 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.

share|improve this question
    
Please have a look at this thread which will answer your question: stackoverflow.com/questions/3858472/… –  KookieMonster Apr 3 '13 at 13:32

5 Answers 5

  1. Create a step 4a, which sends a general e-mail (like that @Max pointed out) that "one of the first 4 steps failed." You can do this using database mail or whatever you're using now to alert on job failure.
  2. Step 4a is set to move to Step 5 on success.
  3. Step 4 is set to move to Step 5 on success.
  4. Steps 1-4 are set to move to step 4a on failure.

If you need to know which step failed, you may just want to put the logic into each step (similar to what Max proposed) to do a TRY/CATCH inside the job step logic rather than build 4 handler steps and use the job scaffolding.

share|improve this answer

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.
share|improve this answer

I don't know if it's the best answer but.

-- Steps 5 and 6 run even when any of steps 1-4 fail.

On the advanced tab of the step change the on failure action to move to the next step.

-- Steps 5 and 6 begin strictly not before steps 1-4 complete. I don't understand this question please clarify

--- When any of steps 1-4 fail, an email notification is sent indicating the step that failed.

Change the on failure action step to run a new step of the job which includes a dbmail script to email you of failure and then set the on success of that step to perfomr the next step of the job

A better option would probably come from some error handling in the script in each of the step runs to email you of failure and use the advanced option in the job to move onto the next step on failure

share|improve this answer

I have a suggestion:

Add a seventh step with the TSQL shown below. This step will send out one email showing all the step's outcome for the specified job for the current day. The TSQL should be run under the msdb context. Set @recipient , @profile_name, and enter-your-job-GUID-here with your values.

EXEC sp_send_dbmail
@profile_name = 'your_current_db_mail_profile_name',
@recipients = 'your_email@companyname.com',
@subject = 'Job steps output',
@body = 'Status of job steps below.',
@execute_query_database = 'msdb',
@query = '
select sj.name,sjh.step_name,
case sjh.run_status 
when 0 then ''Failed''
when 1 then ''Pass''
when 2 then ''Retry''
when 3 then ''Cancel''
end as job_status
from sysjobhistory sjh inner join sysjobs sj ON (sj.job_id=sjh.job_id )
where run_date=convert(int,convert(varchar(8),getdate(),112))
and sj.job_id=''enter-your-job-GUID-here''
'

Set a notification for the job to email you on failure.

Note that this method will only notify you after your job completes as opposed to a step by step email.

You can also try using job tokens (I have not tested this). So replace the last two lines in the TSQL above with:

from sysjobhistory where run_date=$(ESCAPE_SQUOTE(DATE))
and job_id=$(ESCAPE_SQUOTE(JOBID))
share|improve this answer

I'm sure this will not be popular, but consider a trigger on sysjobhistory...example:

USE [msdb]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE   trigger [dbo].[trg_jobstepfailures]
on [dbo].[sysjobhistory]
for insert
as 
declare @strcmd varchar(800),@strRecipient varchar(500),@strMsg varchar(2000), @strServer varchar(255),@strTo varchar(255)

Declare @Subject varchar(500)

if exists (select * from inserted where run_status = 0 and step_name NOT IN ( '(job outcome)', 'do not notify'))
begin
    select  @strMsg =  '<FONT FACE="color: #A0522D; font-family: Verdana; font-size: 12; font-weight:bold">' + @@servername + '-Job: ' + sysjobs.name + '. Step= ' + 
     inserted.step_name + '</font><br><br><FONT FACE="color: #A0522D; font-family: Verdana; font-size: 12">Message ' + inserted.message + '</font>'
     from inserted
    join sysjobs
    on inserted.job_id = sysjobs.job_id
    where inserted.run_status = 0

--subject text
    select @subject = 'Job ' + sysjobs.name + ' Failed on Job Server' +@@Servername
    from inserted 
    join sysjobs 
    on inserted.job_id = sysjobs.job_id
    where inserted.run_status = 0

--recipeints text
    IF @subject like '%<USE NAME OF FAILED JOB HERE>%'
        Begin 
            SET @strRecipient= '<RECPIENT LIST>'
        End
    ELSE
        Begin
            SET @strRecipient= '<ALTERNATE RECIPIENT LIST>'
        End

    exec msdb.dbo.sp_send_dbmail @profile_name = null --uses the default profile
     ,  @recipients = @strRecipient
     ,  @subject = @subject
     ,  @body =  @strMsg
     ,  @body_format = 'HTML' --default is TEXT

end

GO
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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