locked
How to review subscription events? RRS feed

  • Question

  • Hello all,

          I am usjng SQL Server 2008 Std. x64 edition on Windows Server 2008 R2 x64 Std. edition. I have subscribes to some of my reports. for a daily once delivery by email. On first day all subscribers receives their mail sucessfully but from next day no one receives any mail. Now I have to find out root cause for this behaviour. Can anyone tell me how can I review subscription events in SQL Server 2008 ?

    Nilkanth Desai

    Sunday, February 20, 2011 4:24 AM

Answers

  • Hi ,

    You can do the below items

    1) Run the Query on ReportServer DB to check the Status

    select  'JobName' = c.name, 'LastStatus' = s.LastStatus, 'LastRun' = s.LastRunTime, 'ReportPath' = c.Path, 'ReportModifiedBy' = uc.UserName,

    'ScheduleId' = rs.ScheduleId, 'SubscriptionId' = s.SubscriptionID

    from ReportServer.dbo.Subscriptions s join ReportServer.dbo.Catalog c on c.ItemID = s.Report_OID

    left join ReportServer.dbo.ReportSchedule rs on rs.SubscriptionID = s.SubscriptionID

    left join ReportServer.dbo.Users uc on uc.UserID = c.ModifiedByID

    left join ReportServer.dbo.Users us on us.UserID = s.OwnerId

    join msdb.dbo.sysjobs j on j.name = CONVERT(nvarchar(128),rs.ScheduleId)

     

    2) Run The Query to find latest subscription Report delivered.

    select c.Path, c.Name, LastRunTime, replace(LastStatus, 'Mail sent to ','') as lastStatus

    from ReportServer.dbo.Subscriptions s join ReportServer.dbo.Catalog c

    on s.Report_OID = c.ItemID

    order by LastRunTime desc

     

    3) Under Report Manager .i.e. the http://<hostname>\Report . Click on My Subcriptions Link on the Right Corner to check the Status as well.

    4) Check the below two mice link as well

    http://blogs.msdn.com/b/deanka/archive/2009/01/13/diagnosing-and-troubleshooting-subscriptions.aspx

    &

    http://blogs.msdn.com/b/lukaszp/archive/2005/12/30/508328.aspx

     


    Regards, PS
    • Marked as answer by Nilkanth Monday, February 21, 2011 12:28 AM
    Sunday, February 20, 2011 6:12 AM