Script to Access Subscription Data in SQL Server Reporting Services

This script will find out exactly what reports a particular email address is setup to recieve. Simply run it against your ReportServer database:

DECLARE @EmailAddress NVARCHAR(250)

SET @EmailAddress = 'test@test.com'
SELECT
C.Name,
S.[Description] AS [Description],
S.LastRunTime
FROM
Subscriptions S
INNER JOIN [Catalog] C
ON S.Report_OID = C.ItemID
WHERE
S.ExtensionSettings LIKE '%' + @EmailAddress + '%'

You will then need to manually filter through the results, looking at the description in order to see which report is going to who, and when it was last run. This is because you may have a reply to email address set as the email that you are looking for, which would also be returned by the above query.

I wrote this because it was much easier and quicker than trawling through hundreds of subscriptions. Enjoy.

If anyone has any better ways of manipulating the XML contained within the “ExtensionSettings” field on the Subscription table, then please share.

 

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