Clear Up SSRS Subscriptions

One of the things with SSRS subscriptions is that they create a load of Agent Jobs that don’t clearly link back to specific reports.

SQL warns you not to mess with these directly, so you need to find the report and remove or edit the subscriptions via the SSRS interface.

We had loads that were expired, but the question was, what reports to they relate to?

The list below shows a number of Subscriptions that have not run for a while and are not scheduled to run again.

2018-06-12 09_12_38-Job Activity Monitor - ray-m7-sql-01

So, how do we find the associated reports?

If you double click a Job you’ll see the screen below

2018-06-12 09_13_54-Job Activity Monitor - ray-m7-sql-01

Click On “Steps”

2018-06-12 09_14_38-Job Activity Monitor - ray-m7-sql-01

Then Click on “Edit”

2018-06-12 09_15_13-Job Activity Monitor - ray-m7-sql-01

What we want is the @EventData which is the Subscription ID

If you then open MS Management Studio and run the following Code against your SSRS Report Database (replace the id with the one shown in the @EventData above):

FROM dbo.Subscriptions s JOIN dbo.Catalog c
	ON s.Report_OID = c.ItemID
WHERE s.SubscriptionID = '56d31dd0-aca0-420c-940b-7d45f862db1c'

This will then show you the report path and name that the subscription relates to.

You can then connect to your SSRS reports and delete the subscription via the subscription options.

This entry was posted in Programming and tagged , , , . Bookmark the permalink.

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 )

Twitter picture

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

Facebook photo

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

Connecting to %s