none
Getting SSRS report schedules and additional information

    Question

  • Could you please share the script for getting the SSRS report schedules from SQL Server 2005.

    There are a lot of SSRS reports. Could I please get in this format.

    Report Name, Report Description, Report Frequecy/Schedule, Email to whom it is sent, Time taken to run report

    Regards,


    MM

    Thursday, October 10, 2013 2:54 AM

Answers

All replies

  • USE REPORTSERVER

    SELECT * FROM [CATALOG]
    SELECT * FROM [Schedule]
    SELECT * FROM ReportSchedule
    SELECT * FROM Notifications

    SELECT Name,
    CAST(CAST(Content AS VARBINARY(MAX)) AS XML) AS ReportXML
    FROM ReportServer.dbo.Catalog
    where Type = 2
    order by Name

    http://technullogy.wordpress.com/2013/01/24/ssrs-database-tables-explanation/


    Regards, RSingh

    • Proposed as answer by mhm_2004de Thursday, October 10, 2013 4:09 AM
    • Unproposed as answer by Moinu_SQL Thursday, October 10, 2013 8:20 PM
    • Proposed as answer by mhm_2004de Saturday, October 19, 2013 6:08 PM
    • Unproposed as answer by Moinu_SQL Saturday, October 19, 2013 9:04 PM
    Thursday, October 10, 2013 3:57 AM
  • Hi Moinu,

    Thank you for your post. Sorry for my delay.

    In SQL Server Reporting Services (SSRS), a native mode Reporting Services installation uses two databases (ReportServer and ReportServerTempDB) to separate persistent data storage from temporary storage requirements. Subscription and schedule definitions is stores in the ReportServer database. We can use query to get these information.

    You can refer to the following query:
    USE ReportServer
    SELECT c.Name as [Report Name],
    c.Description as [Report Description],
    sch.WeeksInterval AS [Report Frequecy/Schedule],
    s.Description AS [Email to whom it is send],
    Ex.TimeEnd-Ex.TimeStart as [Time taken to run report]
    FROM Catalog c
    INNER JOIN
    Subscriptions s ON c.ItemID = s.Report_OID
    INNER JOIN
    DataSource d ON c.ItemID = d.ItemID
    LEFT OUTER JOIN
    Users u ON u.UserID = c.CreatedByID
    LEFT OUTER JOIN
    ReportSchedule rs ON c.ItemID = rs.ReportID
    LEFT OUTER JOIN
    Schedule sch ON rs.ScheduleID = sch.ScheduleID
    LEFT OUTER JOIN
    ExecutionLogStorage Ex ON Ex.ReportID=S.Report_OID
    WHERE (c.Type = 2)
    ORDER BY c.Name

    More detail information you can refer to the article about View all subscriptions.
    http://blogs.xtivia.com/home/-/blogs/18514

    If you have any questions, please feel free to ask.

    Regards,
    Alisa Tang


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time.
    Thanks for helping make community forums a great place.

    Wednesday, October 16, 2013 3:23 AM
  • Hi Alisa,

    Thank you for the reply. But I am unable to find this ExecutionLogStorage in ReportServer database. This is SQL Server 2005 version. Is there any modified script, I could use.

    The query is failing. Could you please advise.

    Msg 208, Level 16, State 1, Line 2

    Invalid object name 'ExecutionLogStorage'.

    Thank you,

    MM

     

    MM


    • Edited by Moinu_SQL Thursday, October 17, 2013 12:37 AM
    Thursday, October 17, 2013 12:36 AM
  • Hi MM,

    Thank you for your reply. Sorry for my careless.

    In SQL Server Reporting Services 2005, ExecutionLogStorage is replaced by ExecutionLog. So, you can modify ExecutionLogStorage with ExecutionLog to try it again.

    If you have any questions, please feel free to ask.

    Best regards,
    Alisa Tang


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time.
    Thanks for helping make community forums a great place.

    Thursday, October 17, 2013 1:16 AM
  • Hi Alisa,

    Thank you for clarifying so quickly. I still have a few doubts on collecting the right information. Is it possible to get the actual time the report will run. Eg like this

    Report_Name,   Description,      Frequency,    Whom to Send,   Time Taken

    rpt_data        Returns report data     Fri, 9:00PM     gg@**.com            20 min

    Using the script you provided, we are able to get the data for columns 1, 2, 4.

    But for Frequency and Time Taken it is still looking a bit tough. For the frequency column, there is data returned like 1, 2, NULL

    For time taken, the data is getting returned like 1900-01-01 00:01:47.410

    Is it something that could be collected? Could you please help?

    Thank you again.

    Regards,

    MM


    MM



    • Edited by Moinu_SQL Thursday, October 17, 2013 3:17 AM
    Thursday, October 17, 2013 3:16 AM
  • Hi MM,

    Thank you for your post.

    In order to meet your request, I tried to modify the query to get the values you need. We can query schedule StartDate in the Schedule table in ReportServer database. However, it is with the format: 2013-09-20 12:56:00.000. With the value of Time taken to run report, we can add a DateDiff function in our query to get the value we need.

    You can refer to the query below:
    USE ReportServer
    SELECT c.Name as [Report Name],
    c.Description as [Report Description],
    sch.StartDate AS [Report Frequecy/Schedule],
    s.Description AS [Email to whom it is send],
    DateDiff(s,Ex.TimeStart,Ex.TimeEnd) as [Time taken to run report]
    FROM Catalog c
    INNER JOIN
    Subscriptions s ON c.ItemID = s.Report_OID 
    INNER JOIN
    DataSource d ON c.ItemID = d.ItemID
    LEFT OUTER JOIN
    Users u ON u.UserID = c.CreatedByID
    LEFT OUTER JOIN
    ReportSchedule rs ON c.ItemID = rs.ReportID
    LEFT OUTER JOIN
    Schedule sch ON rs.ScheduleID = sch.ScheduleID
    LEFT OUTER JOIN
    ExecutionLogStorage Ex On Ex.ReportID=S.Report_OID
    WHERE (c.Type = 2)
    ORDER BY c.Name

    If you have any questions, please feel free to let me know.

    Regards,
    Alisa Tang


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time.
    Thanks for helping make community forums a great place.


    Friday, October 18, 2013 3:18 AM
  • Hi Alisa,

    Thank you again for the quick reply. You have been of great help. But I am sorry, I have to keep coming back to you with more questions. Regarding the new query you sent me, I am getting output like this.

    When we check the third colum (Report Frequency\schedule), the values are getting repeated again on again for the same date\time. Is there something which we could fix. Thank you again for all the help with the queries.

    Regards,

    MM


    MM

    Saturday, October 19, 2013 12:28 AM
  • Hi MM,

    Thank you for your reply.

    In the query, the value of “Time taken to run report” is record the time we run the report in report manager. If we run the report in report manager with three times, we can get three values for the time taken to run report. So, we have get the repeat records. You can check the ExecutionLog table in ReportServer database.

    Did you want to get the time that the report complete subscription? As per my understanding, it cannot query the time of complete subscription. Sorry for my misunderstanding before. Hope this helps.

    Best Regards,
    Alisa Tang


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time.
    Thanks for helping make community forums a great place.

    Monday, October 21, 2013 2:03 AM
  • Hi Alisa,

    Thank you for the quick response. Yes, I was referring to the time taken for the subscription to complete. Thank you again.

    Regards,

    MM


    MM

    Tuesday, October 22, 2013 3:39 AM