Reportar un Job cuando uno de sus pasos falla

[iislas]

Exacto !!, hay que darle la vuelta, agregas un ultimo paso, que analice que paso con tu job y envie la notificación correspondiente

Código SQL:

EXEC sp_send_dbmail
@profile_name = 'your_current_db_mail_profile_name',
@recipients = '[email protected]',
@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''
'