SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 

SSRS Subscription Schedules – Enhanced

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 CASE	WHEN 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 = CASE	WHEN job_schd.next_run_date = 0
													THEN sys_schd.active_start_date
													ELSE job_schd.next_run_date
												END
							, next_run_time = CASE	WHEN 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 CASE	WHEN 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 = CASE	WHEN job_schd.next_run_date = 0
													THEN sys_schd.active_start_date
													ELSE job_schd.next_run_date
												END
							, next_run_time = CASE	WHEN 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.

Comments

Leave a comment on the original post [jasonbrimhall.info, opens in a new window]

Loading comments...