DROP PROCEDURE dbo.uspEmailJobHistoryRecent
GO
--------------------------------------------------------------------------------
-- =============================================
-- Author: Simon You
-- Create date: Feb 2018
-- Description:
-- =============================================
create procedure dbo.uspEmailJobHistoryRecent
as
begin
set nocount on;
declare
@date datetime,
@body varchar(max),
@EmailProfile dbo.EmailProfile,
@EmailFrom dbo.EmailFrom,
@EmailTo dbo.EmailTo,
@EmailCc dbo.EmailCc,
@EmailCode dbo.EmailCode = 'Job_Report_Recent',
@Subject varchar(max) = 'Job History'
select @Date = GETDATE()
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,
RunDate date,
Runtime time,
RunDateTime datetime,
MinutesElapsed int,
RunDuration RunDuration,
RunStatus RunStatus,
ReportStatus int
);
with cteJob(job_id, name, [enabled], IsSchedule) as
(
select
job_id, name, [enabled],
IsSchedule = CASE WHEN [enabled] = 1 AND
EXISTS (SELECT 1 FROM msdb.dbo.sysjobschedules js, msdb.dbo.sysschedules s
WHERE js.schedule_id = s.schedule_id and js.job_id = j.job_id and s.[enabled] = 1)
THEN 1
ELSE 0
END
from msdb.dbo.sysjobs j
), scheduledJob (job_id) as
(
select distinct
j.job_id
from msdb.dbo.sysjobs j
JOIN msdb.dbo.sysjobschedules js ON j.job_id = js.job_id
JOIN msdb.dbo.sysschedules s ON js.schedule_id = s.schedule_id
where dbo.fnIsSchduledForDate(DATEADD(dd, -1, @Date), s.[enabled], s.freq_type, s.freq_interval, s.freq_relative_interval, s.active_start_date, s.active_end_date) = 1
), cteReport(job_id, name, run_date, run_time, RunDuration, run_status) as
(
SELECT
j.job_id,
j.name,
Run_Date = CAST(CAST(hist.run_date as varchar) AS DATETIME),
Run_time = CAST(DATEADD(ss, hist.run_time % 100, DATEADD(mi, (hist.run_time / 100) % 100, DATEADD(hh, hist.run_time / 10000, '00:00:00'))) AS TIME),
RunDuration = (hist.run_duration / 10000) * 3600 + ((hist.run_duration / 100) % 100) * 60 + hist.run_duration % 100,
hist.run_status
FROM cteJob j
OUTER APPLY
(
select top 1
hist.run_date,
hist.run_time,
hist.run_duration,
hist.run_status
from msdb.dbo.sysjobhistory hist
where hist.job_id = j.job_id and hist.step_id = 0
and hist.run_status in (0, 1, 3) --0: Failed, 1: Succeeded, 3:Cancelled
order by hist.run_date desc, hist.run_time desc
) hist
where j.IsSchedule = 1
)
INSERT INTO @tJobHist(JobName, RunDate, Runtime, RunDateTime, MinutesElapsed, RunDuration, RunStatus, ReportStatus)
select
r.name,
r.run_date,
r.run_time,
RunDateTime = r.run_date + r.run_time,
MinutesElapsed = DATEDIFF(minute, r.run_date + r.run_time, @date),
r.RunDuration,
r.run_status,
ReportStatus = CASE when r.run_status = 0 then 0 --Failed
when r.run_status = 3 then 1 --Cancelled
--when sj.job_id IS NOT NULL AND ISNULL(DATEDIFF(minute, r.run_date + r.run_time, @date), 1441) > 24 * 60 then 2 --Scheduled but not run in last 24 hours
when sj.job_id IS NOT NULL AND DATEDIFF(day, r.run_date, @date) > 1 then 2 --Scheduled but not run in last 24 hours
when r.run_status = 1 then 3 --Succeeded
END
from cteReport r
left join scheduledJob sj on r.job_id = sj.job_id
--select
-- [@bgcolor] = CASE ReportStatus WHEN 0 THEN '#FF0000' --red/#E41B17
-- WHEN 1 THEN '#FFFF00' --yellow/#EAC117
-- WHEN 2 THEN '#800080' --coffee/#808080
-- WHEN 3 then '#00FF00' --green/#4AA02C
-- ELSE '#7D0552' --Plum Velvet/#7D0552
-- END,
-- ReportStatus,
-- color = CASE when ReportStatus in (0, 1, 2) then 'black' else 'white' END,
-- JobName,
-- RunDate = CAST(RunDate AS varchar(10)),
-- RunTime = CAST(RunTime AS varchar(8)),
-- Duration = 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),
-- [status] = case RunStatus when 0 then 'Failed'
-- when 1 then 'Succeeded'
-- when 2 then 'Retry'
-- when 3 then 'Cancelled'
-- end
--from @tJobHist
--order by ReportStatus, JobName
set @Subject = 'SQL Agent Job Status Report'-- for ' + convert(varchar(10), @Date, 120)
print @subject
set @body =
CAST( (
select
[@bgcolor] = CASE ReportStatus WHEN 0 THEN '#FF0000' --red/#E41B17
WHEN 1 THEN '#FFFF00' --yellow/#EAC117
WHEN 2 THEN '#800080' --coffee/#808080
WHEN 3 then '#2ECC40' --green/#00FF00
ELSE '#7D0552' --Plum Velvet/#7D0552
END,
color = CASE when ReportStatus in (0, 1, 2) then 'white' else 'black' END,
td = JobName, '',
td = CAST(RunDate AS varchar(10)), '',
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 'Cancelled'
end, '',
color = 'close'
from @tJobHist
order by ReportStatus, 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)
'
+ '
+ '
' + '
' + '
' + '
' + '
' + '
+ '' +
+ '
+ replace( replace( @body, '<', '<' ), '>', '>' )
+ '
' --00FFFF-->DDDDDD-->39CCCC
set @body = REPLACE(@body, '
set @body = REPLACE(@body, '
set @body = REPLACE(@body, '
')
-- print @body
exec uspSendEmail
@EmailCode = @EmailCode,
@EmailProfile = @EmailProfile,
@EmailFrom = @EmailFrom,
@EmailTo = @EmailTo,
@EmailCc = @EmailCc,
@Subject = @Subject,
@body = @body
end
GO
--exec uspEmailJobHistoryRecent
/*
declare @date date = '2018-02-05'
exec uspEmailJobHistory @date
*/
--select * from dbo.CtsJob
No comments:
Post a Comment