uspEmailJobHistory

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'dbo.uspEmailJobHistory') AND type in (N'P', N'PC'))
DROP PROCEDURE dbo.uspEmailJobHistory
GO

--------------------------------------------------------------------------------
-- =============================================
-- Author: Simon You
-- Create date: Feb 2018
-- Description:
-- =============================================
create procedure dbo.uspEmailJobHistory(@Date date)
as
begin
set nocount on;

declare
@body varchar(max),
@SuccessCount int,
@FailedCount int,
@NotRunCount int,
@EmailProfile dbo.EmailProfile,
@EmailFrom dbo.EmailFrom,
@EmailTo dbo.EmailTo,
@EmailCc dbo.EmailCc,
@EmailCode dbo.EmailCode = 'Job_Report',
@Subject varchar(max) = 'Job History'

if dbo.fnRequireEmail(@Date, @EmailCode) = 0
begin
return
end

select
@EmailProfile = EmailProfile,
@EmailFrom = EmailFrom,
@EmailTo = EmailTo,
@EmailCc = EmailCc
from dbo.SettingEmail
where EmailCode = @EmailCode

if @@ROWCOUNT = 0
begin
return
end

declare @tJobHist table(
JobName sysname,
Runtime time,
RunDuration RunDuration,
RunStatus RunStatus
)

insert into @tJobHist (JobName, RunTime, RunDuration, RunStatus)
select
j.JobName,
hist.RunTime,
hist.RunDuration,
hist.RunStatus
from dbo.CtsJob j
left join
(
select
dtl.CtsJobId,
dtl.RunTime,
dtl.RunDuration,
dtl.RunStatus,
dtl.IsScheduled
from dbo.CtsJobHistoryHeader hdr
join dbo.CtsJobHistoryDetail dtl on hdr.CtsJobHistoryHeaderId = dtl.CtsJobHistoryHeaderId
where hdr.RunDate = @Date
and dtl.IsScheduled = 1
) hist on j.CtsJobId = hist.CtsJobId
where j.ShowInReport = 1
and hist.IsScheduled = 1

select @FailedCount = COUNT(*) from @tJobHist where RunStatus = 0
select @SuccessCount = COUNT(*) from @tJobHist where RunStatus = 1
select @NotRunCount = COUNT(*) from @tJobHist where RunStatus IS NULL

set @Subject = 'SQL Agent Job Status Report for ' + convert(varchar(10), @Date, 120)
-- + ' (Succeeded=' + cast(@SuccessCount as varchar)
-- + ', Failed=' + cast(@FailedCount as varchar)
-- + ', Not Run=' + cast(@NotRunCount as varchar) + ')'
print @subject

  set @body =
CAST( (
select
[@bgcolor] = CASE RunStatus when 0 then '#FF0000' --red/#E41B17
when 1 then '#00FF00' --green/#4AA02C
when 2 then '#800080' --coffee/#808080
when 3 then '#FFFF00'  --yellow/#EAC117
  else '#7D0552' --Plum Velvet/#7D0552
END,
color = CASE when RunStatus in (1, 3) then 'black' else 'white' END,
td = JobName, '',
td = CAST(RunTime AS varchar(8)), '',
[td/@align] = 'right',
td = case when RunDuration > 3600 then cast(RunDuration / 3600 as varchar) + ':' else '' end
+ right('0' + cast((RunDuration % 3600) / 60 as varchar), 2) + ':'
+ right('0' + cast(RunDuration % 60 as varchar), 2), '',
td = case RunStatus when 0 then 'Failed'
when 1 then 'Succeeded'
when 2 then 'Retry'
when 3 then 'Canceled'
end, '',
color = CASE when RunStatus in (1, 3) then 'close' else 'close' END
from @tJobHist
order by case RunStatus when 0 then 0
when 1 then 3
when 2 then 1
when 3 then 2
end, JobName
for xml path( 'tr' ), type
) as varchar(max) )

set @body = 'The following summary displays the "most recent" (latest) job status of the SQL scheduled jobs on Phone Channel Data Warehouse (i.e.: CRSDPSSCA1IDCS\PC)

'
+ ''
+ '' +
+ ' Job NameStart TimeDurationStatus '
+ replace( replace( @body, '<', '<' ), '>', '>' )
+ '
'
set @body = REPLACE(@body, 'white', '')
set @body = REPLACE(@body, 'black', '')
set @body = REPLACE(@body, 'close', '
')
  --print @body
  EXEC msdb.dbo.sp_send_dbmail
@profile_name = @EmailProfile,
@body = @body,
@body_format ='HTML',
@recipients = @EmailTo,
@copy_recipients = @EmailCc,
@subject = @Subject;

insert into dbo.EmailLog(EmailCode, EmailProfile, EmailFrom, EmailTo, EmailCc, EmailSubject, EmailBody)
values (@EmailCode, @EmailProfile, @EmailFrom, @EmailTo, @EmailCc, @Subject, @body)
end
--------------------------------------------------------------------------------
GO

--exec uspEmailJobHistory '2018-02-05'
/*
declare @date date = '2018-02-05'
exec uspEmailJobHistory @date
*/
--select * from dbo.CtsJob