ERMAHBLERG

Thanks to some SMTP changes, we’ve had tons of reports fail recently!!!!

I needed a quick an easy way to see the subscription status of a report, and here is what I have devised:

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'
and LastRunTime > GETDATE()-1.5

order by LastRunTime

The code above will show ALL reporting services reports on Reporting Services 2005 and 2008, and will give the last 36 hours worth of reports. This will filter our new subscriptions, as well as non-data driven subscriptions which were successfully sent.

I plan on revising this, into one which only shows errors, but I am in a time crunch.