Notifica Job Falliti / sys_NotifyFailedJob_sp

CREATE PROCEDURE [dbo].[sys_NotifyFailedJob_sp]
(
   @recipients AS nvarchar(MAX)
 , @subjectPrefix AS nvarchar(100) = NULL
)
AS
BEGIN
 --##2018-02-14 - Boso -        sysjobsteps  // @body
 SET NOCOUNT ON

 DECLARE @JobID AS uniqueidentifier
 DECLARE @JobName AS nvarchar(255)
 DECLARE @subject AS nvarchar(255)
 DECLARE @body AS nvarchar(MAX) = ''
  

 DECLARE #JOBS CURSOR FOR
 SELECT job_id AS j
 FROM msdb..sysjobs j
 WHERE j.enabled <> 0


 OPEN #JOBS

 FETCH NEXT 
 FROM #JOBS 
 INTO @JOBID

 WHILE @@FETCH_STATUS = 0
  BEGIN
   DECLARE @PrevInstance AS int
   DECLARE @ErrCount AS int

   
   -- CERCA LA FINE DELLA History DELL'ULTIMA ESECUZIONE DEL JOB
   SELECT TOP 1 @PrevInstance = sjh.instance_id
   FROM msdb..sysjobhistory sjh WITH (NOLOCK)
   WHERE sjh.job_id = @JobID AND 
     sjh.step_id = 0    --> 0 = (Job outcome)
   ORDER BY
     sjh.instance_id DESC
     

   SELECT @PrevInstance = ISNULL(@PrevInstance, 0)


   -- CERCA PER I JOB ANDATI IN ERRORE
   SELECT @ErrCount = COUNT(*)
   FROM msdb..sysjobhistory sjh WITH (NOLOCK)
   WHERE sjh.job_id = @JobID AND 
     sjh.instance_id >= @PrevInstance AND 
     sjh.step_id = 0 AND   --> 0 = (Job outcome)
     sjh.run_status = 0
   


   -- CERCA SE UNO STEP DEL JOB E' ANDATO IN ERRORE
   SELECT @ErrCount = COUNT(*)
   FROM msdb..sysjobsteps s
   WHERE s.job_id = @JobID
    AND s.last_run_outcome = 0



   SELECT @ErrCount = ISNULL(@ErrCount, 0)


   -- MANDA LA MAIL
   IF @ErrCount > 0
    BEGIN
     SELECT   @jobName = j.name
       , @subject = LTRIM(ISNULL(@subjectPrefix, '') + ' Job "' + j.name + '" terminato CON ERRORI!')
     FROM msdb..sysjobs AS j WITH (NOLOCK)
     WHERE j.job_id = @JobID 



     -- COMPONE IL BODY DELLA MAIL
     SET @body = N'Job name:   ' + @JobName


     SELECT TOP 1 
       @body += N'
Step name:  ' + x.step_name + '

Message:
--------
' + x.message
     FROM msdb..sysjobhistory x
     WHERE x.run_status = 0
      AND x.job_id = @JobID
     ORDER BY 
       x.run_date DESC, x.run_time DESC



     SELECT TOP 1 
       @body += N'

Subsystem:  ' + s.subsystem + '
Command:    ' + s.command
   FROM msdb..sysjobsteps s
   WHERE s.job_id = @JobID
    AND s.last_run_outcome = 0
   ORDER BY 
     s.last_run_date DESC, s.last_run_time DESC



     EXEC msdb..sp_send_dbmail
        @recipients = @recipients
      , @subject =  @subject
      , @body_format =  'TEXT'
      , @body =  @body
    END
   
   
   FETCH NEXT 
   FROM #JOBS 
   INTO @JOBID
  END

 CLOSE #JOBS
 DEALLOCATE #JOBS

END
GO