Reporting Services Subscription Status

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
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.


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s