Hi,
Is there any way to know on which all day SSRS Mail subscription got failed for a specific report. i am trying to find it out on which all date subscription for Report x is failed. Please share your expertise.
Thanks
Hi,
Is there any way to know on which all day SSRS Mail subscription got failed for a specific report. i am trying to find it out on which all date subscription for Report x is failed. Please share your expertise.
Thanks
Try this
SELECT sc.ScheduleID
,c.Name AS ReportName
,sb.[Description] AS [Subscription Description]
,sb.DeliveryExtension AS [Delivery Type]
,sb.LastStatus AS [Last Run Status]
,sb.LastRunTime AS [Last Run Time]
,c.Path AS ReportPath
FROM ReportServer.dbo.ReportSchedule rs
INNER JOIN ReportServer.dbo.Schedule sc ON rs.ScheduleID = sc.ScheduleID
INNER JOIN ReportServer.dbo.Subscriptions sb ON rs.SubscriptionID = sb.SubscriptionID
INNER JOIN ReportServer.dbo.[Catalog] c ON rs.ReportID = c.ItemID AND sb.Report_OID = c.ItemID
WHERE (sb.LastStatus LIKE 'Failure%' OR sb.LastStatus LIKE 'Error%')
AND sb.LastRunTime > DATEADD(D, -1, GETDATE())
Thanks Ahmed,
But The problem is the report was failed in the past for couple of days,
and now its running perfectly, i am looking for how many days report was failed and on which all days.
the report was scheduled to execute once in a day.
appreciate your suggestion
Thanks
you can change the last where condition in the query to go back to the number of days you want.