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;

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

142

143

144

145

146

147

148

149

150

151

152

153

154

155

156

157

158

159

160

161

162

163

164

165

166

167

168

169

170

171

172

173

174

175

176

177

178

179

180

181

182

183

184

185

186

187

188

189

190

191

192

193

194

195

196

197

198

199

200

201

202

203

204

205

206

207

208

209

210

211

212

213

214

215

216

217

218

219

220

221

222

223

224

225

226

227

228

229

230

231

232

233

234

235

236

237

238

239

240

241

242

243

244

245

246

247

248

249

250

251

252

253

254

255

256

257

258

259

260

261

262

263

264

265

266

267

268

269

270

271

272

273

274

275

276

277

278

279

280

281

282

283

284

285

286

287

288

289

290

291

292

293

294

295

296

297

298

299

300

301

302

303

304

305

306

307

308

309

310

311

312

313

314

315

316

317

318

319

320

321

322

323

324

325

326

327

328

329

330

331

332

333

334

335

336

337

338

339

340

341

342

343

344

345

346

347

348

349

350

351

352

353

354

355

356

357

358

359

360

361

362

363

364

365

366

367

368

369

370

371

372

373

374

375

376

377

378

379

380

381

382

383

384

385

386

387

388

389

390

391

392

393

394

395

396

397

398

399

400

401

402

403

404

405

406

407

408

409

410

411

412

413

414

415

416

417

418

419

420

421

422

423

424

425

426

427

428

429

430

431

432

433

434

435

436

437

438

439

440

441

442

443

444

445

446

447

448

449

450

451

452

453

454

455

456

457

458

459

460

461

462

463

464

465

466

467

468

469

470

471

472

473

474

475

476

477

478

479

480

481

482

483

484

485

486

487

488

489

490

491

492

493

494

495

496

497

498

499

500

501

502

503

504

505

506

507

508

509

510

511

512

513

514

515

516

517

518

519

520

521

522

523

524

525

526

527

528

529

530

531

532

533

534

535

536

537

538

539

540

541

542

543

544

545

546

547

548

549

550

551

552

553

554

555

556

557

558

559

560

561

562

563

564

565

566

567

568

569

570

571

572

573

574

575

576

577

578

579

580

581

582

583

584

585

586

587

588

589

590

591

592

593

594

595

596

597

598

599

600

601

602

603

604

605

606

607

608

609

610

611

612

613

614

615

616

617

618

619

620

621

622

623

624

625

626

627

628

629

630

631

632

633

634

635

636

637

638

639

640

641

642

643

644

645

646

647

648

649

650

651

652

653

654

655

656

657

658

659

660

661

662

663

664

665

666

667

668

669

670

671

672

673

674

675

676

677

678

679

/* 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.

SSRS Custom Shared Schedule May 22, 2017
SSRS Subscription Schedules May 23, 2017
CRM Data Source Connection Error January 23, 2020
Syspolicy Phantom Health Records April 6, 2018
Capture the Flag - The Trace Flag December 6, 2018

SQL RNNR