Query SSRS Report Schedules
A few months back I needed to grab SSRS schedule information through T-SQL, essentially in a query so I can publish the data through an EXCEL sheet (with a data connection to the database).
Generally, to grab SQL Server Reporting Service report schedules, there are two ways:
- By querying the SSRS web service
- By querying the job agent tables in the MSDB database, and the Report Schedule table in the Report Server Catalog database
Using the SSRS web service, you can call the ListSchedules method to get a list of all schedules.
Alternatively, the scheduling information could also be found in the following tables:
- ReportServer$[Instance-Name].dbo.ReportSchedule: Contains the SQL Server Job Agent ScheduleID.
- msdb.dbo.sysjobs: Contains the ScheduleID (as a column called “Name”) and JobID (for Job Agent Job)
- msdb.dbo.sysjobschedules: Contains the scheduling information.
And putting everything together…
SELECT
[Locale],
[InactiveFlags],
'Next Run Date' = 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,
'Next Run Time' = isnull(CASE len(next_run_time)
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,'NA'),
Convert(XML,[ExtensionSettings]).value('(//ParameterValue/Value[../Name="TO"])[1]','nvarchar(50)') as [To]
,Convert(XML,[ExtensionSettings]).value('(//ParameterValue/Value[../Name="RenderFormat"])[1]','nvarchar(50)') as [Render Format]
,Convert(XML,[ExtensionSettings]).value('(//ParameterValue/Value[../Name="Subject"])[1]','nvarchar(50)') as [Subject]
---Example report parameters: StartDateMacro, EndDateMacro & Currency.
,Convert(XML,[Parameters]).value('(//ParameterValue/Value[../Name="StartDateMacro"])[1]','nvarchar(50)') as [Start Date]
,Convert(XML,[Parameters]).value('(//ParameterValue/Value[../Name="EndDateMacro"])[1]','nvarchar(50)') as [End Date]
,Convert(XML,[Parameters]).value('(//ParameterValue/Value[../Name="Currency"])[1]','nvarchar(50)') as [Currency]
,[ModifiedDate]
,[LastStatus]
,[EventType]
,[LastRunTime]
,[DeliveryExtension]
,[Version]
FROM
ReportServer$LIVE.dbo.[Subscriptions] S
INNER JOIN ReportServer$LIVE.dbo.ReportSchedule R ON S.SubscriptionID = R.SubscriptionID
INNER JOIN msdb.dbo.sysjobs J ON Convert(nvarchar(128),R.ScheduleID) = J.name
INNER JOIN msdb.dbo.sysjobschedules JS ON J.job_id = JS.job_id
[Locale],
[InactiveFlags],
'Next Run Date' = 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,
'Next Run Time' = isnull(CASE len(next_run_time)
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,'NA'),
Convert(XML,[ExtensionSettings]).value('(//ParameterValue/Value[../Name="TO"])[1]','nvarchar(50)') as [To]
,Convert(XML,[ExtensionSettings]).value('(//ParameterValue/Value[../Name="RenderFormat"])[1]','nvarchar(50)') as [Render Format]
,Convert(XML,[ExtensionSettings]).value('(//ParameterValue/Value[../Name="Subject"])[1]','nvarchar(50)') as [Subject]
---Example report parameters: StartDateMacro, EndDateMacro & Currency.
,Convert(XML,[Parameters]).value('(//ParameterValue/Value[../Name="StartDateMacro"])[1]','nvarchar(50)') as [Start Date]
,Convert(XML,[Parameters]).value('(//ParameterValue/Value[../Name="EndDateMacro"])[1]','nvarchar(50)') as [End Date]
,Convert(XML,[Parameters]).value('(//ParameterValue/Value[../Name="Currency"])[1]','nvarchar(50)') as [Currency]
,[ModifiedDate]
,[LastStatus]
,[EventType]
,[LastRunTime]
,[DeliveryExtension]
,[Version]
FROM
ReportServer$LIVE.dbo.[Subscriptions] S
INNER JOIN ReportServer$LIVE.dbo.ReportSchedule R ON S.SubscriptionID = R.SubscriptionID
INNER JOIN msdb.dbo.sysjobs J ON Convert(nvarchar(128),R.ScheduleID) = J.name
INNER JOIN msdb.dbo.sysjobschedules JS ON J.job_id = JS.job_id
Note: This has been tested on SQL Server 08 and 08 R2 only, although I reckon it would still work with SQL Server 05
0 votes
Which version of SSRS is this indended for?
Msg 207, Level 16, State 1, Line 25
Invalid column name ‘ExtensionSettings’.
Msg 207, Level 16, State 1, Line 26
Invalid column name ‘ExtensionSettings’.
Msg 207, Level 16, State 1, Line 27
Invalid column name ‘ExtensionSettings’.
Msg 207, Level 16, State 1, Line 29
Invalid column name ‘Parameters’.
Msg 207, Level 16, State 1, Line 30
Invalid column name ‘Parameters’.
Msg 207, Level 16, State 1, Line 31
Invalid column name ‘Parameters’.
Msg 207, Level 16, State 1, Line 32
Invalid column name ‘ModifiedDate’.
Msg 207, Level 16, State 1, Line 33
Invalid column name ‘LastStatus’.
Msg 207, Level 16, State 1, Line 34
Invalid column name ‘EventType’.
Msg 207, Level 16, State 1, Line 35
Invalid column name ‘LastRunTime’.
Msg 207, Level 16, State 1, Line 36
Invalid column name ‘DeliveryExtension’.
Msg 207, Level 16, State 1, Line 37
Invalid column name ‘Version’.
Ah, my bad.
I forgot to join on the “Subscriptions” table in the SSRS Catalogue DB.
I have edited the script above to include that table.
Thank you for flagging the issue!
I found the query quite useful, however it was missing the report name and the location to make it easy to identify. I have modified the query to include those items:
SELECT
c.[Path],
c.[Name],
[Locale],
[InactiveFlags],
‘Next Run Date’ = 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,
‘Next Run Time’ = isnull(CASE len(next_run_time)
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,’NA’),
Convert(XML,[ExtensionSettings]).value(‘(//ParameterValue/Value[../Name=”TO”])[1]’,’nvarchar(50)’) as [To]
,Convert(XML,[ExtensionSettings]).value(‘(//ParameterValue/Value[../Name=”RenderFormat”])[1]’,’nvarchar(50)’) as [Render Format]
,Convert(XML,[ExtensionSettings]).value(‘(//ParameterValue/Value[../Name=”Subject”])[1]’,’nvarchar(50)’) as [Subject]
—Example report parameters: StartDateMacro, EndDateMacro & Currency.
,Convert(XML,[Parameters]).value(‘(//ParameterValue/Value[../Name=”StartDateMacro”])[1]’,’nvarchar(50)’) as [Start Date]
,Convert(XML,[Parameters]).value(‘(//ParameterValue/Value[../Name=”EndDateMacro”])[1]’,’nvarchar(50)’) as [End Date]
,Convert(XML,[Parameters]).value(‘(//ParameterValue/Value[../Name=”Currency”])[1]’,’nvarchar(50)’) as [Currency]
,s.[ModifiedDate]
,[LastStatus]
,[EventType]
,[LastRunTime]
,[DeliveryExtension]
,[Version]
FROM
ReportServer.dbo.[Subscriptions] S
INNER JOIN ReportServer.dbo.ReportSchedule R ON S.SubscriptionID = R.SubscriptionID
INNER JOIN msdb.dbo.sysjobs J ON Convert(nvarchar(128),R.ScheduleID) = J.name
INNER JOIN msdb.dbo.sysjobschedules JS ON J.job_id = JS.job_id
LEFT JOIN [ReportServer].[dbo].[Catalog] C on R.[ReportID]= c.ItemID
Thanks Sergio!
Hey,
I’ve updated the code to also look at enabled and disabled schedules. Many thanks for this query ;-):
SELECT DISTINCT
–[Locale],
C.Name AS ReportName
,C.PATH AS ReportManagerPath
,S.Description AS SubscriptionDescription
,[EventType]
,CASE WHEN j.enabled = 1 THEN ‘Enabled’ ELSE ‘Disabled’ END AS ScheduledJob
,’NextRunDate’ = CASE WHEN J.enabled =1 THEN CAST(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 + ‘ ‘ +
ISNULL(CASE LEN(next_run_time)
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,’NA’) AS DATETIME) ELSE ’01/01/1900 00:00:00′ END
,CONVERT(XML,[ExtensionSettings]).value(‘(//ParameterValue/Value[../Name=”TO”])[1]’,’nvarchar(50)’) AS [To]
,CONVERT(XML,[ExtensionSettings]).value(‘(//ParameterValue/Value[../Name=”Subject”])[1]’,’nvarchar(50)’) AS [Subject]
,CONVERT(XML,[ExtensionSettings]).value(‘(//ParameterValue/Value[../Name=”RenderFormat”])[1]’,’nvarchar(50)’) AS [Render Format]
—Example report parameters: StartDateMacro, EndDateMacro & Currency.
,CONVERT(XML,[Parameters]).value(‘(//ParameterValue/Value[../Name=”StartDateMacro”])[1]’,’nvarchar(50)’) AS [Start Date]
,CONVERT(XML,[Parameters]).value(‘(//ParameterValue/Value[../Name=”EndDateMacro”])[1]’,’nvarchar(50)’) AS [End Date]
,CONVERT(XML,[Parameters]).value(‘(//ParameterValue/Value[../Name=”Currency”])[1]’,’nvarchar(50)’) AS [Currency]
,[LastRunTime]
,S.[ModifiedDate]
,[LastStatus]
,[DeliveryExtension]
,[Version]
,[InactiveFlags]
FROM
ReportServer.dbo.[Subscriptions] S
INNER JOIN ReportServer.dbo.ReportSchedule R ON S.SubscriptionID = R.SubscriptionID
INNER JOIN CATALOG C ON R.ReportID = C.ItemID
INNER JOIN msdb.dbo.sysjobs J ON CONVERT(nvarchar(128),R.ScheduleID) = J.name
INNER JOIN msdb.dbo.sysjobschedules JS ON J.job_id = JS.job_id
amazing! thanks for sharing
Hi guys, someone can modify the ATIF Script in order to let me select between days in the column NextRunDate. The purpose of this modification is that I would like to check if in one specific date and time there won’t reports to executed, otherwise I could not restart the service.
SELECT DISTINCT
–[Locale],
C.Name AS ReportName
,C.PATH AS ReportManagerPath
,S.Description AS SubscriptionDescription
,[EventType]
,CASE WHEN j.enabled = 1 THEN ‘Enabled’ ELSE ‘Disabled’ END AS ScheduledJob
,’NextRunDate’ = CASE WHEN J.enabled =1 THEN CAST(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 + ‘ ‘ +
ISNULL(CASE LEN(next_run_time)
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,’NA’) AS DATETIME) ELSE ’01/01/1900 00:00:00′ END
,CONVERT(XML,[ExtensionSettings]).value(‘(//ParameterValue/Value[../Name=”TO”])[1]‘,’nvarchar(50)’) AS [To]
,CONVERT(XML,[ExtensionSettings]).value(‘(//ParameterValue/Value[../Name=”Subject”])[1]‘,’nvarchar(50)’) AS [Subject]
,CONVERT(XML,[ExtensionSettings]).value(‘(//ParameterValue/Value[../Name=”RenderFormat”])[1]‘,’nvarchar(50)’) AS [Render Format]
—Example report parameters: StartDateMacro, EndDateMacro & Currency.
,CONVERT(XML,[Parameters]).value(‘(//ParameterValue/Value[../Name=”StartDateMacro”])[1]‘,’nvarchar(50)’) AS [Start Date]
,CONVERT(XML,[Parameters]).value(‘(//ParameterValue/Value[../Name=”EndDateMacro”])[1]‘,’nvarchar(50)’) AS [End Date]
,CONVERT(XML,[Parameters]).value(‘(//ParameterValue/Value[../Name=”Currency”])[1]‘,’nvarchar(50)’) AS [Currency]
,[LastRunTime]
,S.[ModifiedDate]
,[LastStatus]
,[DeliveryExtension]
,[Version]
,[InactiveFlags]
FROM
ReportServer.dbo.[Subscriptions] S
INNER JOIN ReportServer.dbo.ReportSchedule R ON S.SubscriptionID = R.SubscriptionID
INNER JOIN CATALOG C ON R.ReportID = C.ItemID
INNER JOIN msdb.dbo.sysjobs J ON CONVERT(nvarchar(128),R.ScheduleID) = J.name
INNER JOIN msdb.dbo.sysjobschedules JS ON J.job_id = JS.job_id