SQLTeam.com | Weblogs | Forums

Subscription Error In SSRS


#1

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


#2

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())


#3

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


#4

you can change the last where condition in the query to go back to the number of days you want.