none
[Reloaded] Informing Users when the subscriptions fail

    Question

  • I am a SharePoint admin who is running SP2010 SP1 and SSRS 2008 R2 in integrated mode.

    Everytime I patch SharePoint, Users start complaining about several subscriptions which did not execute because of the SP patching.

    They want to know how can they find out which subscriptions did not get executed. I had asked this question on msdn forum before and received this query as an answer

    SELECT C.Name, S.LastRunTime, S.LastStatus, S.Description
    FROM Subscriptions AS S
    LEFT OUTER JOIN [Catalog] AS C
    ON C.ItemID = S.Report_OID
    WHERE LEFT (S.LastStatus, 12) != 'Mail sent to'
    AND LEFT (S.LastStatus, 12) != 'New Subscrip'

    When I execute this in my environment... this is what I see

    I don't know what to make out of "Done: 1 Processesd of 1 total; 0 errors.". Is this a success message or a failure message?

    As per the MSDN Answer... this query is suppored to return me the FAILED SUBSCRPTIONS!!!

    But it looks like that its returning executions with 0 errors.

    I want a query which will give me the reports which FAILED to execute subscriptions. (site collection information will also be very helpful).


    val it: unit=()

    Wednesday, March 20, 2013 1:27 PM

All replies

  • A simple check for failed reports:

    Select *
    FROM [ReportServer].[dbo].[Subscriptions]
    where LastStatus like 'Failure%' or LastStatus like 'An error%'

    I wrote the following job to resubmit failed report subscriptions and run it every morning at 8am from SQL Agent:

    DECLARE @EVENT           char(50)
    DECLARE @PROCESSCOUNTER  INT
    DECLARE @PROCESSFINALCNT INT
    DECLARE @EventTable         table
    (Row                        int,
     SubscriptionID             uniqueidentifier
     )
     
    SET NOCOUNT ON;

    insert into @EventTable
    SELECT ROW_NUMBER() over (order by SubscriptionID) as row, subscriptionID
    FROM [ReportServer].[dbo].[Subscriptions]
    where LastStatus like 'Failure%' or LastStatus like 'An error%'

    SET @ProcessCounter = 1
    SET @ProcessFinalCnt = (SELECT MAX(row) FROM @EventTable) 

    While @ProcessCounter <= @ProcessFinalCnt
    BEGIN
    set @EVENT = (select subscriptionid from @EventTable where Row = @PROCESSCOUNTER)
    --print @event
    exec ReportServer.dbo.AddEvent @EventType='TimedSubscription', @EventData=@EVENT

    SET @processcounter = @processcounter+1
    END

    Wednesday, March 20, 2013 2:31 PM
  • When I execute the query

    Select *
    FROM [ReportServer].[dbo].[Subscriptions]
    where LastStatus like 'Failure%' or LastStatus like 'An error%'

    I get no records. So probably the subscription didn't fail... but they were missed because the server was being patched and the service was down.

    So SSRS did not make any attempt to execute them.

    So this changes my problem statement.

    find out failed and missed subscriptions in SSRS.


    val it: unit=()

    Thursday, March 21, 2013 7:15 AM
  • Hi,

    Thank you for your question.

    I am trying to involve someone more familiar with this topic for a further look at this issue. Sometime delay might be expected from the job transferring. Your patience is greatly appreciated.

    Thank you for your understanding and support.

    Regards,
    Mike Yin

    If you have any feedback on our support, please click here


    Mike Yin
    TechNet Community Support

    Monday, April 01, 2013 1:00 PM