Query SSRS Report Schedules

26 Jun
June 26, 2011

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

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

7 replies
  1. Ceduljko says:

    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’.

    Reply
  2. admin says:

    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!

    Reply
  3. Sergio says:

    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

    Reply
  4. Atif says:

    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

    Reply
  5. Cuervo says:

    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

    Reply

Leave a Reply

Want to join the discussion?
Feel free to contribute!

Leave a Reply to Ceduljko Cancel reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>