none
How to get a Report subscription Information - SSRS 2014 RRS feed

  • Question

  • Hi Everyone,

    I have a requirement where we have to update a column in a table based on the execution date of the report subscription.

    Is there a way in the report server database where we know the success or failure of the report subscription job based on which we can populate a column in other table?

    Thanks a lot for all the help.

    Monday, June 1, 2020 10:27 PM

Answers

  • Hi Sh08,

    Thanks for reply.

    Yes, you can use subscriptionID which is a 16-byte GUID (uniqueidentifier) to identify the subscription.

    Best Regards,

    Amelia


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by Sh08 Tuesday, July 14, 2020 12:55 PM
    Wednesday, June 3, 2020 1:24 AM

All replies

  • Hi Sh08,

    You can use the following query from Olaf’s article to get the last status and last run time of subscription:

    USE [ReportServer];  -- You may change the database name. 
    GO 
    
    SELECT USR.UserName AS SubscriptionOwner 
          ,SUB.ModifiedDate 
          ,SUB.[Description] 
          ,SUB.EventType 
          ,SUB.DeliveryExtension 
          ,SUB.LastStatus 
          ,SUB.LastRunTime 
          ,SCH.NextRunTime 
          ,SCH.Name AS ScheduleName       
          ,CAT.[Path] AS ReportPath 
          ,CAT.[Description] AS ReportDescription 
    FROM dbo.Subscriptions AS SUB 
         INNER JOIN dbo.Users AS USR 
             ON SUB.OwnerID = USR.UserID 
         INNER JOIN dbo.[Catalog] AS CAT 
             ON SUB.Report_OID = CAT.ItemID 
         INNER JOIN dbo.ReportSchedule AS RS 
             ON SUB.Report_OID = RS.ReportID 
                AND SUB.SubscriptionID = RS.SubscriptionID 
         INNER JOIN dbo.Schedule AS SCH 
             ON RS.ScheduleID = SCH.ScheduleID 
    ORDER BY USR.UserName 
            ,CAT.[Path];
    

    You can add a new datasource and dataset for reportserver database.

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

    Best Regards,

    Amelia



    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, June 2, 2020 2:10 AM
  • Thanks a lot for your help. I will try it.

    Also, If I need to identify the subscription uniquely, can I use the subscription_ID?

    is this similar to the GUID which is created when the sql server agent job is created on the SQL Server (for the report subscription)?

    Thanks,

    Sana

    Tuesday, June 2, 2020 12:49 PM
  • Hi Sh08,

    Thanks for reply.

    Yes, you can use subscriptionID which is a 16-byte GUID (uniqueidentifier) to identify the subscription.

    Best Regards,

    Amelia


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by Sh08 Tuesday, July 14, 2020 12:55 PM
    Wednesday, June 3, 2020 1:24 AM
  • Hi Sh08,

    Did the answers above help you?

    Please feel free to let us know if you have any other question.

    If you find any post in the thread is helpful, you could kindly mark it as answer. This would benefit the community, and encourage the community member to keep working on your issues.

    Best Regards,

    Amelia


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, June 8, 2020 3:16 AM
  • Thanks a lot. It did help me to find the details of the subscription.

    But the challenge I am having is that as the report subscription is a data subscription, it has multiple executions and we need to determine all the data driven executions ran successfully and then update the column in the database.

    All I found was the 'LastStatus' column in the Subscriptions table. When ever all the report executions with in the report subscription ran fine, it has '0 errors.' at the end.

    Do you think it will be reliable way to find if the data driven subscription ran successfully.

    My where clause is something like this, so far it works fine.

    Select * from

    Subscriptions

    WHERE

    SubscriptionID =<GUID Value>

     AND LASTSTATUS LIKE'Done%'

    AND LASTSTATUS LIKE'%0 errors.'


    • Edited by Sh08 Tuesday, July 14, 2020 12:48 AM
    Tuesday, July 14, 2020 12:47 AM