none
SSRS Email subscription in "Pending" status RRS feed

  • Question

  • Hi, 

    I've created 4 SSRS email subscriptions. The subscriptions were running fine till they became in "Pending" status.

    I've tried more than one solution to fix this issue. The only workaround was to recreate these subscriptions and after some running times they became to "Pending" status again.

    additional info:
    -The subscription scheduled to run depend on shared schedule that runs every Monday 5:00 AM.

    - All reports are retrieving  small number of records and takes little time (may be 3 to 6 seconds) to execute, Except one of these reports takes more time to execute (may be 20 to 30 seconds).

    Help please.

    Monday, November 21, 2016 1:31 PM

All replies

  • Hi Bin,

    'select * from ReportServer..ExecutionLog2 where RequestType = 'Subscription'

    use ReportServer
    go

     SELECT S.ScheduleID AS SQLAgent_Job_Name
          , SUB.Description AS Sub_Desc
          , SUB.DeliveryExtension AS Sub_Del_Extension
          , C.Name AS ReportName
          , C.Path AS ReportPath
     FROM ReportSchedule RS JOIN Schedule S ON RS.ScheduleID = S.ScheduleID
          JOIN Subscriptions SUB ON RS.SubscriptionID = SUB.SubscriptionID
          JOIN Catalog C ON RS.ReportID = C.ItemID AND SUB.Report_OID = C.ItemID
     WHERE C.Name LIKE '%%' 

    Refer


    Please Mark it as Answered if it answered your question OR mark it as Helpful if it help you to solve your problem.

    Monday, November 21, 2016 2:39 PM
  • Hi Bin Ga3fer,

    From your description, firstly please check the log usually located in  C:\Program Files\Microsoft SQL Server\MSRS13.MSSQLSERVER\Reporting Services\LogFiles to find more detailed information for this issue.

    Then please use the query like below to check if the all the subscription in the database are showing in the report server.

    SELECT Reportname = c.Name 
          ,SubscriptionDesc=su.Description 
          ,Subscriptiontype=su.EventType 
          ,su.LastStatus 
          ,su.LastRunTime 
          ,Schedulename=sch.Name 
          ,ScheduleType = sch.EventType 
          ,ScheduleFrequency = 
           CASE sch.RecurrenceType 
           WHEN 1 THEN 'Once' 
           WHEN 2 THEN 'Hourly' 
           WHEN 4 THEN 'Daily/Weekly' 
           WHEN 5 THEN 'Monthly' 
           END 
           ,su.Parameters 
      FROM Reportserver.dbo.Subscriptions su 
      JOIN Reportserver.dbo.Catalog c 
        ON su.Report_OID = c.ItemID 
      JOIN Reportserver.dbo.ReportSchedule rsc 
        ON rsc.ReportID = c.ItemID 
       AND rsc.SubscriptionID = su.SubscriptionID 
      JOIN Reportserver.dbo.Schedule Sch 
    ON rsc.ScheduleID = sch.ScheduleID
    

    To resolve the issue, please try to clean up the subscription from the database or you can try to restart the corresponding job by using following query to find the Job ID.

    Use ReportServer
    SELECT    b.name AS JobName
                , e.name
                , e.path
                , d.description
                , a.SubscriptionID
                , laststatus
                , eventtype
                , LastRunTime
                , date_created
                , date_modified
    FROM  ReportServer.dbo.ReportSchedule   a
    JOIN  msdb.dbo.sysjobs b 
          ON cast(a.ScheduleID as varchar(255)) = b.name
    JOIN  ReportServer.dbo.ReportSchedule c
          ON b.name = CAST(c.ScheduleID AS VARCHAR(255))
    JOIN             
          (select eventtype,laststatus,LastRunTime,d.SubscriptionID,d.description, cast(d.report_oid as varchar(255)) report_oid from  ReportServer.dbo.Subscriptions d  ) d 
           on c.SubscriptionID = d.SubscriptionID
    JOIN
          (select e.name, e.path, cast(e.itemid as varchar(255)) itemid from ReportServer.dbo.Catalog e   ) e 
          on itemid = report_oid
    

    Reference:

    Best Regards,
    Pirlo Zhang


    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.

    • Proposed as answer by Pirlo Zhang Tuesday, December 6, 2016 10:16 AM
    Tuesday, November 22, 2016 5:58 AM
  • Hi Bin,

    Did you resolve the issue? If not, would you please share more detailed with us for analysis?

    Thanks,

    Pirlo Zhang


    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, December 6, 2016 10:16 AM
  • Hi Bin,

    I do have same issue and i could not able to solve it. Can you please help me out.

    Regards,

    JSteve

    Wednesday, December 4, 2019 6:57 AM