Reporting Services
Over the past couple of articles I have illustrated some of the fun that can be had when dealing with the scheduling capabilities of Reporting Services (SSRS). The first article covered how to create more advanced schedules (from the SSRS point of view). In another I article, I showed how to retrieve scheduling information from the ReportServer database. In that last article, I also promised a follow-up article for more in-depth scheduling details.
SSRS provides the capability to review the scheduled reports (subscriptions) in a far moare detailed fashion than shown in that previous article. That ability is held within the ReportServer database. This article will dive into the source of this scheduling information within the ReportServer database.
This dive will be a bit more detailed than the first time I dove into SSRS scheduling – here. That particular dive was missing an important set of data.
Deeper Dive
My first dive into building a report of the report schedules was pretty comprehensive and I used it quite frequently. Many others also used it regularly for their environments as well. So the first attempt wasn’t bad by any stretch. The main problem (at least for now) is that the script does not account for any of the custom schedules that can be built. I have to be honest in that I hadn’t really considered that feasibility. Times and experience change that perspective. When that perspective changes, it is time to dive back in and add coverage for the shortcoming in the script.
When I dove back in to fetch the custom scheduling information, 
I realized there was a discrepancy even in the old report in that I was not gathering Job information pertinent to the schedule. Recall that SSRS subscriptions are performed via the SQL Agent. With that in mind, it is reasonable that the Agent job information is pertinent and germane to the report subscription and probably should be included in a report. Couple that with the methods on creating custom schedules for the SSRS reports, and we have a resounding need to ensure that data is trapped properly.
Due to this epiphany, I have now a more complete script to include both the data from SQL Agent as well as the data from the ReportServer database in regards to subscriptions and schedules of reports.
Script
In pulling the data together from the two sources, I opted to return two result sets. Not just two disparate result sets, but rather two result sets that each pertained to both the agent job information as well as the ReportServer scheduling data. For instance, I took all of the subscriptions in the ReportServer and joined that data to the job system to glean information from there into one result set. And I did the reverse as well. You will see when looking at the query and data. One of the reasons for doing it this way was to make this easier to assimilate into an SSRS style report.
/* union the ssrs schedule query with the sql agent schedule query to get all
report schedules
*/IF OBJECT_ID('tempdb..#morepower') IS NOT NULL DROP TABLE #morepower;
IF OBJECT_ID('tempdb..#ReportJobScheds') IS NOT NULL DROP TABLE #ReportJobScheds;
IF OBJECT_ID('tempdb..#SSRSScheds') IS NOT NULL DROP TABLE #SSRSScheds;
--DROP TABLE #morepower;
--DROP TABLE #ReportJobScheds;
--DROP TABLE #SSRSScheds;
DECLARE @ReportName VARCHAR(100)
SET @ReportName = NULL;
CREATE TABLE #morepower (MonthDate BIGINT,N BIGINT,PowerN BIGINT PRIMARY KEY CLUSTERED
,NameofMonth VARCHAR(25),WkDay VARCHAR(25))
;
WITH powers(powerN, n) AS (
SELECT POWER(2,number), number
FROM master.dbo.spt_values
WHERE type = 'P' AND number < 31)
INSERT INTO #morepower ( MonthDate ,N,PowerN ,NameofMonth ,WkDay)
SELECT ROW_NUMBER() OVER (ORDER BY N) AS MonthDate,N,PowerN
,CASE WHEN N BETWEEN 0 AND 11
THEN DateName(month,DATEADD(month,N+1,0)-1)
ELSE NULL
END AS NameofMonth
,CASE WHEN N BETWEEN 0 AND 6
THEN DATENAME(weekday,DATEADD(DAY,n+1,0)-2)
ELSE NULL
END AS WkDay
FROM powers
SELECT DISTINCT
ReportScheduleID = s.ScheduleID
,Ca.Path as ReportManagerPath,Ca.Name as ReportName
,U.UserName as SubscriptionCreator
,PrimaryScheduleSource = 'SSRS Catalog'
,Su.Description as SubscriptionDescription
,SSRSScheduleStartDate = S.StartDate
,Su.LastRunTime
,CASE
WHEN s.RecurrenceType = 1 THEN 'Once'
WHEN s.RecurrenceType = 2 THEN 'Hourly'
WHEN s.RecurrenceType = 3 THEN 'Daily'
WHEN s.RecurrenceType = 4 THEN 'Weekly'
WHEN s.RecurrenceType = 5 THEN 'Monthly'
WHEN s.RecurrenceType = 6 THEN 'Week of Month'
END AS RecurrenceType
, [Sched Enabled] = CASE su.InactiveFlags
WHEN 0 THEN 'Yes'
ELSE 'No'
END
,s.EventType
,ISNULL(REPLACE(REPLACE(STUFF(
(Select ', ['+CONVERT(VARCHAR(20),MonthDate)+']' AS [text()]
FROM #morepower m1
WHERE m1.powerN < s.DaysofMonth+1
AND s.DaysofMonth & m1.powerN >0
ORDER BY N FOR XML PATH(''), TYPE).value('.','VARCHAR(MAX)')
, 1, 2, ''),'[',''),']','')
,'N/A') AS DaysofMonth
,ISNULL(c1.NameOfMonth,'N/A') AS MonthString
,ISNULL(c2.WkDays,'N/A') AS DaysofWeek
,CASE MonthlyWeek
WHEN 1 THEN 'First'
WHEN 2 THEN 'Second'
WHEN 3 THEN 'Third'
WHEN 4 THEN 'Fourth'
WHEN 5 THEN 'Last'
ELSE 'N/A'
END AS MonthlyWeek
,ISNULL(CONVERT(VARCHAR(10),s.DaysInterval),'N/A') AS DaysInterval
,ISNULL(CONVERT(VARCHAR(10),s.MinutesInterval),'N/A') AS MinutesInterval
,ISNULL(CONVERT(VARCHAR(10),s.WeeksInterval),'N/A') AS WeeksInterval
into #SSRSScheds
FROM #morepower mp, dbo.Schedule s
INNER JOIN ReportSchedule RS
ON S.ScheduleID = RS.ScheduleID
INNER JOIN Catalog Ca
ON Ca.ItemID = RS.ReportID
INNER JOIN Subscriptions Su
ON Su.SubscriptionID = RS.SubscriptionID
INNER JOIN Users U
ON U.UserID = S.CreatedById
OR U.UserID = Su.OwnerID
CROSS APPLY (Select s.ScheduleID,REPLACE(REPLACE(STUFF(
(SELECT ', ['+ NameofMonth + ']' AS [text()]
FROM #morepower m1 ,dbo.Schedule s1
WHERE m1.NameofMonth IS NOT NULL
AND m1.powerN & s1.Month >0
AND s1.ScheduleID = s.ScheduleID
ORDER BY N FOR XML PATH(''), TYPE).value('.','VARCHAR(MAX)')
, 1, 2, ''),'[',''),']','') AS NameOfMonth)c1
CROSS APPLY (SELECT s.ScheduleID,REPLACE(REPLACE(STUFF(
(SELECT ', [' + WkDay + ']' AS [text()]
FROM #morepower m1 ,dbo.Schedule s2
WHERE m1.WkDay IS NOT NULL
AND DaysOfWeek & m1.powerN >0
AND s2.ScheduleID = s.ScheduleID
ORDER BY N FOR XML PATH(''), TYPE).value('.','VARCHAR(MAX)')
, 1, 2, ''),'[',''),']','') AS WkDays) c2
WHERE Ca.Name = ISNULL(@ReportName,Ca.Name);
SELECT [Job ID] = job.job_id
, [Job Name] = job.name
, [Job Enabled] = CASE job.enabled
WHEN 1 THEN 'Yes'
WHEN 0 THEN 'No'
END
, [JobSchedID] = sched.schedule_id
, PrimaryScheduleSource = 'SQL Agent Job Catalog'
, [RecurrenceType] = CASE sched.freq_type
WHEN 1 THEN 'Once'
WHEN 4 THEN 'Daily'
WHEN 8 THEN 'Weekly'
WHEN 16 THEN 'Monthly'
WHEN 32 THEN 'Monthly relative'
WHEN 64 THEN 'When SQLServer Agent starts'
END
, [Sched Enabled] = CASE sched.enabled
WHEN 1 THEN 'Yes'
WHEN 0 THEN 'No'
END
, EventType = 'SharedSchedule'
, CASE sched.freq_type
WHEN 1 THEN ''
WHEN 4 THEN convert(VARCHAR,sched.freq_interval) + ' days'
WHEN 8 THEN c2.WkDays
WHEN 16 THEN 'Day of Month'
WHEN 32 THEN case sched.freq_interval
WHEN 1 THEN 'Sunday'
WHEN 2 THEN 'Monday'
WHEN 3 THEN 'Tuesday'
WHEN 4 THEN 'Wednesday'
WHEN 5 THEN 'Thursday'
WHEN 6 THEN 'Friday'
WHEN 7 THEN 'Saturday'
WHEN 8 THEN 'Day'
WHEN 9 THEN 'Weekday'
WHEN 10 THEN 'Weekend Day'
END
WHEN 64 THEN ''
WHEN 128 THEN ''
END as [Frequency Interval]
, [Relative Interval] = CASE sched.freq_relative_interval
WHEN 1 THEN 'First'
WHEN 2 THEN 'Second'
WHEN 4 THEN 'Third'
WHEN 8 THEN 'Fourth'
WHEN 16 THEN 'Last'
ELSE NULL
END
, sched.freq_recurrence_factor
, [Next Run Date] = convert(datetime,CASE next_run_date
WHEN 0 THEN NULL
ELSE SUBSTRING(CONVERT(VARCHAR(15), next_run_date),
1, 4) + '/'
+ SUBSTRING(CONVERT(VARCHAR(15), next_run_date),
5, 2) + '/'
+ SUBSTRING(CONVERT(VARCHAR(15), next_run_date),
7, 2)
END + ' ' + CASE LEN(next_run_time)
WHEN 1
THEN CAST('00:00:0' + RIGHT(next_run_time, 2) AS CHAR(8))
WHEN 2
THEN CAST('00:00:' + RIGHT(next_run_time, 2) AS CHAR(8))
WHEN 3
THEN CAST('00:0' + LEFT(RIGHT(next_run_time, 3),
1) + ':'
+ RIGHT(next_run_time, 2) AS CHAR(8))
WHEN 4
THEN CAST('00:' + LEFT(RIGHT(next_run_time, 4),
2) + ':'
+ RIGHT(next_run_time, 2) AS CHAR(8))
WHEN 5
THEN CAST('0' + LEFT(RIGHT(next_run_time, 5), 1)
+ ':' + LEFT(RIGHT(next_run_time, 4), 2)
+ ':' + RIGHT(next_run_time, 2) AS CHAR(8))
WHEN 6
THEN CAST(LEFT(RIGHT(next_run_time, 6), 2) + ':'
+ LEFT(RIGHT(next_run_time, 4), 2) + ':'
+ RIGHT(next_run_time, 2) AS CHAR(8))
END)
, [Max Duration] = CASE LEN(run_duration)
WHEN 1
THEN CAST('00:00:0' + CAST(run_duration AS CHAR) AS CHAR(8))
WHEN 2
THEN CAST('00:00:' + CAST(run_duration AS CHAR) AS CHAR(8))
WHEN 3
THEN CAST('00:0' + LEFT(RIGHT(run_duration, 3),
1) + ':'
+ RIGHT(run_duration, 2) AS CHAR(8))
WHEN 4
THEN CAST('00:' + LEFT(RIGHT(run_duration, 4), 2)
+ ':' + RIGHT(run_duration, 2) AS CHAR(8))
WHEN 5
THEN CAST('0' + LEFT(RIGHT(run_duration, 5), 1)
+ ':' + LEFT(RIGHT(run_duration, 4), 2)
+ ':' + RIGHT(run_duration, 2) AS CHAR(8))
WHEN 6
THEN CAST(LEFT(RIGHT(run_duration, 6), 2) + ':'
+ LEFT(RIGHT(run_duration, 4), 2) + ':'
+ RIGHT(run_duration, 2) AS CHAR(8))
END
, [Subday Frequency] = CASE ( sched.freq_subday_interval )
WHEN 0 THEN 'Once'
ELSE CAST('Every '
+ RIGHT(sched.freq_subday_interval,
2) + ' '
+ CASE ( sched.freq_subday_type )
WHEN 1 THEN 'Once'
WHEN 4 THEN 'Minutes'
WHEN 8 THEN 'Hours'
END AS CHAR(16))
END
, [Sched End Date] = sched.active_end_date
, [Sched End Time] = sched.active_end_time
, [AgentScheduleStartDate] = convert(datetime,CASE sched.active_start_date
WHEN 0 THEN NULL
ELSE SUBSTRING(CONVERT(VARCHAR(15), sched.active_start_date),
1, 4) + '/'
+ SUBSTRING(CONVERT(VARCHAR(15), sched.active_start_date),
5, 2) + '/'
+ SUBSTRING(CONVERT(VARCHAR(15), sched.active_start_date),
7, 2)
END + ' ' + CASE LEN(sched.active_start_time)
WHEN 1
THEN CAST('00:00:0' + RIGHT(sched.active_start_time, 2) AS CHAR(8))
WHEN 2
THEN CAST('00:00:' + RIGHT(sched.active_start_time, 2) AS CHAR(8))
WHEN 3
THEN CAST('00:0' + LEFT(RIGHT(sched.active_start_time, 3),
1) + ':'
+ RIGHT(sched.active_start_time, 2) AS CHAR(8))
WHEN 4
THEN CAST('00:' + LEFT(RIGHT(sched.active_start_time, 4),
2) + ':'
+ RIGHT(sched.active_start_time, 2) AS CHAR(8))
WHEN 5
THEN CAST('0' + LEFT(RIGHT(sched.active_start_time, 5), 1)
+ ':' + LEFT(RIGHT(sched.active_start_time, 4), 2)
+ ':' + RIGHT(sched.active_start_time, 2) AS CHAR(8))
WHEN 6
THEN CAST(LEFT(RIGHT(sched.active_start_time, 6), 2) + ':'
+ LEFT(RIGHT(sched.active_start_time, 4), 2) + ':'
+ RIGHT(sched.active_start_time, 2) AS CHAR(8))
END)
, LastRunTime = Q1.LastRun
, ca.ReportScheduleID
into #ReportJobScheds
FROM msdb.dbo.sysjobs job
CROSS APPLY ( SELECT js.job_id, SUBSTRING(js.command, CHARINDEX('@EventData=', js.command) + 12, 36) as ReportScheduleID
FROM msdb.dbo.sysjobsteps js
WHERE js.command LIKE '%AddEvent @EventType=%'
) ca
CROSS APPLY ( SELECT CASEWHEN TRY_CONVERT(UNIQUEIDENTIFIER, j.name) IS NULL
THEN j.name
ELSE NULL
END AS jobname
FROM msdb.dbo.sysjobs j
WHERE j.job_id = job.job_id
) caj
LEFT JOIN ( SELECT job_schd.job_id
, sys_schd.enabled
, sys_schd.schedule_id
, sys_schd.freq_type
, sys_schd.freq_interval
, sys_schd.freq_relative_interval
, sys_schd.freq_recurrence_factor
, sys_schd.freq_subday_type
, sys_schd.freq_subday_interval
, next_run_date = CASEWHEN job_schd.next_run_date = 0
THEN sys_schd.active_start_date
ELSE job_schd.next_run_date
END
, next_run_time = CASEWHEN job_schd.next_run_date = 0
THEN sys_schd.active_start_time
ELSE job_schd.next_run_time
END
, active_end_date = NULLIF(sys_schd.active_end_date,
'99991231')
, active_end_time = NULLIF(sys_schd.active_end_time,
'235959')
, sys_schd.active_start_date
, sys_schd.active_start_time
FROM msdb.dbo.sysjobschedules job_schd
LEFT JOIN msdb.dbo.sysschedules sys_schd
ON job_schd.schedule_id = sys_schd.schedule_id
) sched
ON job.job_id = sched.job_id
LEFT OUTER JOIN ( SELECT job_id
, MAX(job_his.run_duration) AS run_duration
,MAX(CAST(
STUFF(STUFF(CAST(job_his.run_date as varchar),7,0,'-'),5,0,'-') + ' ' +
STUFF(STUFF(REPLACE(STR(job_his.run_time,6,0),' ','0'),5,0,':'),3,0,':') as datetime)) AS [LastRun]
FROM msdb.dbo.sysjobhistory job_his
GROUP BY job_id
) Q1
ON job.job_id = Q1.job_id
CROSS APPLY (SELECT sched.schedule_id,REPLACE(REPLACE(STUFF(
(SELECT ', [' + WkDay + ']' AS [text()]
FROM #morepower m1 ,msdb.dbo.sysschedules s2
WHERE m1.WkDay IS NOT NULL
AND freq_interval & m1.powerN >0
AND s2.schedule_id = sched.schedule_id
ORDER BY N FOR XML PATH(''), TYPE).value('.','VARCHAR(MAX)')
, 1, 2, ''),'[',''),']','') AS WkDays) c2
WHERE job.job_id = ca.job_id
AND caj.jobname IS NOT NULL;
SELECT sub.ReportScheduleID
, rjs.[Job Name]
, sub.ReportManagerPath
, sub.ReportName
, sub.SubscriptionCreator
, rjs.PrimaryScheduleSource
, sub.SubscriptionDescription
, sub.SSRSScheduleStartDate
, sub.LastRunTime
, sub.RecurrenceType
, sub.[Sched Enabled]
, sub.EventType
, sub.DaysofMonth
, sub.MonthString
, sub.DaysofWeek
, sub.MonthlyWeek
, sub.DaysInterval
, sub.MinutesInterval
, sub.WeeksInterval
FROM #ReportJobScheds rjs
INNER JOIN (SELECT DISTINCT
ReportScheduleID = s.ScheduleID
,Ca.Path as ReportManagerPath
,Ca.Name as ReportName
,U.UserName as SubscriptionCreator
--,PrimaryScheduleSource = 'SSRS Catalog'
,Su.Description as SubscriptionDescription
,SSRSScheduleStartDate = S.StartDate
,Su.LastRunTime
,CASE
WHEN s.RecurrenceType = 1 THEN 'Once'
WHEN s.RecurrenceType = 2 THEN 'Hourly'
WHEN s.RecurrenceType = 3 THEN 'Daily'
WHEN s.RecurrenceType = 4 THEN 'Weekly'
WHEN s.RecurrenceType = 5 THEN 'Monthly'
WHEN s.RecurrenceType = 6 THEN 'Week of Month'
END AS RecurrenceType
, [Sched Enabled] = CASE su.InactiveFlags
WHEN 0 THEN 'Yes'
ELSE 'No'
END
,s.EventType
,ISNULL(REPLACE(REPLACE(STUFF(
(Select ', ['+CONVERT(VARCHAR(20),MonthDate)+']' AS [text()]
FROM #morepower m1
WHERE m1.powerN < s.DaysofMonth+1
AND s.DaysofMonth & m1.powerN >0
ORDER BY N FOR XML PATH(''), TYPE).value('.','VARCHAR(MAX)')
, 1, 2, ''),'[',''),']','')
,'N/A') AS DaysofMonth
,ISNULL(c1.NameOfMonth,'N/A') AS MonthString
,ISNULL(c2.WkDays,'N/A') AS DaysofWeek
,CASE MonthlyWeek
WHEN 1 THEN 'First'
WHEN 2 THEN 'Second'
WHEN 3 THEN 'Third'
WHEN 4 THEN 'Fourth'
WHEN 5 THEN 'Last'
ELSE 'N/A'
END AS MonthlyWeek
,ISNULL(CONVERT(VARCHAR(10),s.DaysInterval),'N/A') AS DaysInterval
,ISNULL(CONVERT(VARCHAR(10),s.MinutesInterval),'N/A') AS MinutesInterval
,ISNULL(CONVERT(VARCHAR(10),s.WeeksInterval),'N/A') AS WeeksInterval
FROM #morepower mp, dbo.Schedule s
INNER JOIN ReportSchedule RS
ON S.ScheduleID = RS.ScheduleID
INNER JOIN Catalog Ca
ON Ca.ItemID = RS.ReportID
INNER JOIN Subscriptions Su
ON Su.SubscriptionID = RS.SubscriptionID
INNER JOIN Users U
ON U.UserID = S.CreatedById
OR U.UserID = Su.OwnerID
CROSS APPLY (Select s.ScheduleID,REPLACE(REPLACE(STUFF(
(SELECT ', ['+ NameofMonth + ']' AS [text()]
FROM #morepower m1 ,dbo.Schedule s1
WHERE m1.NameofMonth IS NOT NULL
AND m1.powerN & s1.Month >0
AND s1.ScheduleID = s.ScheduleID
ORDER BY N FOR XML PATH(''), TYPE).value('.','VARCHAR(MAX)')
, 1, 2, ''),'[',''),']','') AS NameOfMonth)c1
CROSS APPLY (SELECT s.ScheduleID,REPLACE(REPLACE(STUFF(
(SELECT ', [' + WkDay + ']' AS [text()]
FROM #morepower m1 ,dbo.Schedule s2
WHERE m1.WkDay IS NOT NULL
AND DaysOfWeek & m1.powerN >0
AND s2.ScheduleID = s.ScheduleID
ORDER BY N FOR XML PATH(''), TYPE).value('.','VARCHAR(MAX)')
, 1, 2, ''),'[',''),']','') AS WkDays) c2
WHERE Ca.Name = ISNULL(@ReportName,Ca.Name)) sub
ON sub.ReportScheduleID = rjs.ReportScheduleID
UNION ALL
SELECT ss.ReportScheduleID
, [Job Name] = CONVERT(VARCHAR(64),ss.ReportScheduleID)
, ss.ReportManagerPath
, ss.ReportName
, ss.SubscriptionCreator
, ss.PrimaryScheduleSource
, ss.SubscriptionDescription
, ss.SSRSScheduleStartDate
, ss.LastRunTime
, ss.RecurrenceType
, ss.[Sched Enabled]
, ss.EventType
, ss.DaysofMonth
, ss.MonthString
, ss.DaysofWeek
, ss.MonthlyWeek
, ss.DaysInterval
, ss.MinutesInterval
, ss.WeeksInterval
From #SSRSScheds ss;
SELECT sub.[Job ID]
, sub.[Job Name]
, sub.[Job Enabled]
, sub.JobSchedID
, rjs.PrimaryScheduleSource
, sub.RecurrenceType
, sub.[Sched Enabled]
, sub.EventType
, sub.[Frequency Interval]
, sub.[Relative Interval]
, sub.freq_recurrence_factor
, sub.[Next Run Date]
, sub.[Max Duration]
, sub.[Subday Frequency]
, sub.[Sched End Date]
, sub.[Sched End Time]
, sub.AgentScheduleStartDate
, sub.LastRunTime
, rjs.ReportScheduleID
, InStepScheduleID = sub.ReportScheduleID -- sometimes the scheduleid in the step is different from the actual scheduleid
FROM #SSRSScheds rjs
LEFT OUTER JOIN (SELECT [Job ID] = job.job_id
, [Job Name] = caj.jobname
, [Job Enabled] = CASE job.enabled
WHEN 1 THEN 'Yes'
WHEN 0 THEN 'No'
END
, [JobSchedID] = sched.schedule_id
, PrimaryScheduleSource = 'SQL Agent Job Catalog'
, [RecurrenceType] = CASE sched.freq_type
WHEN 1 THEN 'Once'
WHEN 4 THEN 'Daily'
WHEN 8 THEN 'Weekly'
WHEN 16 THEN 'Monthly'
WHEN 32 THEN 'Monthly relative'
WHEN 64 THEN 'When SQLServer Agent starts'
END
, [Sched Enabled] = CASE sched.enabled
WHEN 1 THEN 'Yes'
WHEN 0 THEN 'No'
END
, EventType = 'SharedSchedule'
, CASE sched.freq_type
WHEN 1 THEN ''
WHEN 4 THEN convert(VARCHAR,sched.freq_interval) + ' days'
WHEN 8 THEN c2.WkDays
WHEN 16 THEN 'Day of Month'
WHEN 32 THEN case sched.freq_interval
WHEN 1 THEN 'Sunday'
WHEN 2 THEN 'Monday'
WHEN 3 THEN 'Tuesday'
WHEN 4 THEN 'Wednesday'
WHEN 5 THEN 'Thursday'
WHEN 6 THEN 'Friday'
WHEN 7 THEN 'Saturday'
WHEN 8 THEN 'Day'
WHEN 9 THEN 'Weekday'
WHEN 10 THEN 'Weekend Day'
END
WHEN 64 THEN ''
WHEN 128 THEN ''
END as [Frequency Interval]
, [Relative Interval] = CASE sched.freq_relative_interval
WHEN 1 THEN 'First'
WHEN 2 THEN 'Second'
WHEN 4 THEN 'Third'
WHEN 8 THEN 'Fourth'
WHEN 16 THEN 'Last'
ELSE NULL
END
, sched.freq_recurrence_factor
, [Next Run Date] = convert(datetime,CASE next_run_date
WHEN 0 THEN NULL
ELSE SUBSTRING(CONVERT(VARCHAR(15), next_run_date),
1, 4) + '/'
+ SUBSTRING(CONVERT(VARCHAR(15), next_run_date),
5, 2) + '/'
+ SUBSTRING(CONVERT(VARCHAR(15), next_run_date),
7, 2)
END + ' ' + CASE LEN(next_run_time)
WHEN 1
THEN CAST('00:00:0' + RIGHT(next_run_time, 2) AS CHAR(8))
WHEN 2
THEN CAST('00:00:' + RIGHT(next_run_time, 2) AS CHAR(8))
WHEN 3
THEN CAST('00:0' + LEFT(RIGHT(next_run_time, 3),
1) + ':'
+ RIGHT(next_run_time, 2) AS CHAR(8))
WHEN 4
THEN CAST('00:' + LEFT(RIGHT(next_run_time, 4),
2) + ':'
+ RIGHT(next_run_time, 2) AS CHAR(8))
WHEN 5
THEN CAST('0' + LEFT(RIGHT(next_run_time, 5), 1)
+ ':' + LEFT(RIGHT(next_run_time, 4), 2)
+ ':' + RIGHT(next_run_time, 2) AS CHAR(8))
WHEN 6
THEN CAST(LEFT(RIGHT(next_run_time, 6), 2) + ':'
+ LEFT(RIGHT(next_run_time, 4), 2) + ':'
+ RIGHT(next_run_time, 2) AS CHAR(8))
END)
, [Max Duration] = CASE LEN(run_duration)
WHEN 1
THEN CAST('00:00:0' + CAST(run_duration AS CHAR) AS CHAR(8))
WHEN 2
THEN CAST('00:00:' + CAST(run_duration AS CHAR) AS CHAR(8))
WHEN 3
THEN CAST('00:0' + LEFT(RIGHT(run_duration, 3),
1) + ':'
+ RIGHT(run_duration, 2) AS CHAR(8))
WHEN 4
THEN CAST('00:' + LEFT(RIGHT(run_duration, 4), 2)
+ ':' + RIGHT(run_duration, 2) AS CHAR(8))
WHEN 5
THEN CAST('0' + LEFT(RIGHT(run_duration, 5), 1)
+ ':' + LEFT(RIGHT(run_duration, 4), 2)
+ ':' + RIGHT(run_duration, 2) AS CHAR(8))
WHEN 6
THEN CAST(LEFT(RIGHT(run_duration, 6), 2) + ':'
+ LEFT(RIGHT(run_duration, 4), 2) + ':'
+ RIGHT(run_duration, 2) AS CHAR(8))
END
, [Subday Frequency] = CASE ( sched.freq_subday_interval )
WHEN 0 THEN 'Once'
ELSE CAST('Every '
+ RIGHT(sched.freq_subday_interval,
2) + ' '
+ CASE ( sched.freq_subday_type )
WHEN 1 THEN 'Once'
WHEN 4 THEN 'Minutes'
WHEN 8 THEN 'Hours'
END AS CHAR(16))
END
, [Sched End Date] = sched.active_end_date
, [Sched End Time] = sched.active_end_time
, [AgentScheduleStartDate] = convert(datetime,CASE sched.active_start_date
WHEN 0 THEN NULL
ELSE SUBSTRING(CONVERT(VARCHAR(15), sched.active_start_date),
1, 4) + '/'
+ SUBSTRING(CONVERT(VARCHAR(15), sched.active_start_date),
5, 2) + '/'
+ SUBSTRING(CONVERT(VARCHAR(15), sched.active_start_date),
7, 2)
END + ' ' + CASE LEN(sched.active_start_time)
WHEN 1
THEN CAST('00:00:0' + RIGHT(sched.active_start_time, 2) AS CHAR(8))
WHEN 2
THEN CAST('00:00:' + RIGHT(sched.active_start_time, 2) AS CHAR(8))
WHEN 3
THEN CAST('00:0' + LEFT(RIGHT(sched.active_start_time, 3),
1) + ':'
+ RIGHT(sched.active_start_time, 2) AS CHAR(8))
WHEN 4
THEN CAST('00:' + LEFT(RIGHT(sched.active_start_time, 4),
2) + ':'
+ RIGHT(sched.active_start_time, 2) AS CHAR(8))
WHEN 5
THEN CAST('0' + LEFT(RIGHT(sched.active_start_time, 5), 1)
+ ':' + LEFT(RIGHT(sched.active_start_time, 4), 2)
+ ':' + RIGHT(sched.active_start_time, 2) AS CHAR(8))
WHEN 6
THEN CAST(LEFT(RIGHT(sched.active_start_time, 6), 2) + ':'
+ LEFT(RIGHT(sched.active_start_time, 4), 2) + ':'
+ RIGHT(sched.active_start_time, 2) AS CHAR(8))
END)
, LastRunTime = Q1.LastRun
, ca.ReportScheduleID
FROM msdb.dbo.sysjobs job
CROSS APPLY ( SELECT js.job_id, SUBSTRING(js.command, CHARINDEX('@EventData=', js.command) + 12, 36) as ReportScheduleID
FROM msdb.dbo.sysjobsteps js
WHERE js.command LIKE '%AddEvent @EventType=%'
) ca
CROSS APPLY ( SELECT CASEWHEN TRY_CONVERT(UNIQUEIDENTIFIER, j.name) IS NULL
THEN NULL
ELSE j.name
END AS jobname
FROM msdb.dbo.sysjobs j
WHERE j.job_id = job.job_id
) caj
LEFT JOIN ( SELECT job_schd.job_id
, sys_schd.enabled
, sys_schd.schedule_id
, sys_schd.freq_type
, sys_schd.freq_interval
, sys_schd.freq_relative_interval
, sys_schd.freq_recurrence_factor
, sys_schd.freq_subday_type
, sys_schd.freq_subday_interval
, next_run_date = CASEWHEN job_schd.next_run_date = 0
THEN sys_schd.active_start_date
ELSE job_schd.next_run_date
END
, next_run_time = CASEWHEN job_schd.next_run_date = 0
THEN sys_schd.active_start_time
ELSE job_schd.next_run_time
END
, active_end_date = NULLIF(sys_schd.active_end_date,
'99991231')
, active_end_time = NULLIF(sys_schd.active_end_time,
'235959')
, sys_schd.active_start_date
, sys_schd.active_start_time
FROM msdb.dbo.sysjobschedules job_schd
LEFT JOIN msdb.dbo.sysschedules sys_schd
ON job_schd.schedule_id = sys_schd.schedule_id
) sched
ON job.job_id = sched.job_id
LEFT OUTER JOIN ( SELECT job_id
, MAX(job_his.run_duration) AS run_duration
,MAX(CAST(
STUFF(STUFF(CAST(job_his.run_date AS VARCHAR),7,0,'-'),5,0,'-') + ' ' +
STUFF(STUFF(REPLACE(STR(job_his.run_time,6,0),' ','0'),5,0,':'),3,0,':') AS DATETIME)) AS [LastRun]
FROM msdb.dbo.sysjobhistory job_his
GROUP BY job_id
) Q1
ON job.job_id = Q1.job_id
CROSS APPLY (SELECT sched.schedule_id,REPLACE(REPLACE(STUFF(
(SELECT ', [' + WkDay + ']' AS [text()]
FROM #morepower m1 ,msdb.dbo.sysschedules s2
WHERE m1.WkDay IS NOT NULL
AND freq_interval & m1.powerN >0
AND s2.schedule_id = sched.schedule_id
ORDER BY N FOR XML PATH(''), TYPE).value('.','VARCHAR(MAX)')
, 1, 2, ''),'[',''),']','') AS WkDays) c2
WHERE job.job_id = ca.job_id
) sub
ON sub.[Job Name] = rjs.ReportScheduleID
UNION ALL
SELECT rjs.[Job ID]
, rjs.[Job Name]
, rjs.[Job Enabled]
, rjs.JobSchedID
, rjs.PrimaryScheduleSource
, rjs.RecurrenceType
, rjs.[Sched Enabled]
, rjs.EventType
, rjs.[Frequency Interval]
, rjs.[Relative Interval]
, rjs.freq_recurrence_factor
, rjs.[Next Run Date]
, rjs.[Max Duration]
, rjs.[Subday Frequency]
, rjs.[Sched End Date]
, rjs.[Sched End Time]
, rjs.AgentScheduleStartDate
, rjs.LastRunTime
, rjs.ReportScheduleID
, InStepScheduleID = rjs.ReportScheduleID
FROM #ReportJobScheds rjs;As you can see, it is not a short script. By fair measure, it is also considerably more complex than the XML version that was recently posted (and mentioned earlier in this article). That said, it is many times more flexible and complete than the XML version as well. I do continue to use the bit math for figuring the schedules as I did in the first version of the script. When done this way, I can handle the custom schedules as well as get extensive details about the schedule from both the msdb and ReportServer databases.
Recap
SSRS provides built-in mechanisms to help report on the scheduled reports that have been deployed. This version of the report will help you retrieve the data from both a job perspective and from the report scheduler perspective. Through this series of articles, you should be confident in being able to now create custom schedules as well as accurately report on any reports that have specific subscriptions/schedules.
