September 8, 2022 at 8:50 am
You would have to code it into the job step using a try catch block, and inside the catch block you would send the email to say that the step failed, and then you would set the continuation pieces correctly on failure if you wanted to terminate the job or carry on to the next step etc.
BEGIN TRY
Do the work
END TRY
BEGIN CATCH
Send email on failure
END CATCH
September 8, 2022 at 9:17 am
Using the try catch above sounds like the best approach for sql step. If it's not a sql step and you can't modify it, you could add an email job step, so that if step 6 fails, it goes to step 7 which sends an email and then goes to step 8, but if step 6 succeeds it goes straight to step 8 and bypassed the email step. This assumes you only need a single step to behave this way. If you need multiple it would be a bit too convoluted.
September 8, 2022 at 1:26 pm
I use this as the last step in multi-step jobs (with job steps set to go to next step on failure).
Note: I did not write this, and have lost track of who did. If anybody knows the source, please let us know so that they can get the credit
declare
@jobId uniqueidentifier,
@instanceID int,
@recipients varchar(1000) = 'DBA@YourCompany.com',
@profileName varchar(255) = 'EmailProfile',
@msg varchar(1000),
@failureCount smallint,
@jobName varchar(1000),
@subject varchar(1000),
@failureCounter as smallint = 1
set @jobId = CONVERT(uniqueidentifier, $(ESCAPE_NONE(JOBID))) ;;
--set @jobId = $(ESCAPE_SQUOTE(JOBID));
set @instanceID = coalesce((select max(instance_id) FROM msdb.dbo.sysjobhistory
where job_id = @jobId AND step_id = 0), 0);
---------------Fetch List of Step Errors------------
select *
into #Errs
from
(
select
rank() over (partition by step_id order by step_id) rn,
row_number() over (partition by step_id order by run_date desc, run_time desc) ReverseTryOrder,
j.name job_name,
run_status,
step_id,
step_name,
[message]
from msdb.dbo.sysjobhistory h
inner join msdb.dbo.sysjobs j on
j.job_id = h.job_id
where instance_id > @instanceID
and h.job_id = @jobId
) as agg
where ReverseTryOrder = 1 ---Pick the last retry attempt of each step
and run_status <> 1 -- show only those that didn't succeed
set @failureCount = isnull(@@rowcount,0)---Stored here because we'll still need the rowcount after it's reset.
-------------------------If there are any failures assemble email and send ------------------------------------------------
if @failureCount <> 0
begin
declare @pluralS as char(1) = case when @failureCount > 1 then 's' else '' end ---To make it look like a computer knows English
select top 1 @subject= 'Job: ' + job_name + ' had ' + cast(@failureCount as varchar(3)) + ' step' + @pluralS + ' that failed',
@msg = 'The trouble is... ' +char(13) + char(10)+char(13) + char(10)
from dbo.#Errs
while @failureCounter <= @failureCount
begin
select @msg = @msg + 'Step:' + CAST(step_id as varchar(3)) + ': ' + step_name +char(13) + char(10)
+ [message] +char(13) + char(10)+char(13) + char(10) from dbo.#Errs
where rn = @failureCounter
set @failureCounter = @failureCounter + 1
end
exec msdb.dbo.sp_send_dbmail
@recipients = @recipients,
@subject = @subject,
@profile_name = @profileName,
@body = @msg
END
September 8, 2022 at 2:24 pm
Thanks everyone sorted now
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply