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

Advertisements

Leave a Reply

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

WordPress.com Logo

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

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s