Jump to content


Check out our Community Blogs





- - - - -

Clear Up SSRS Subscriptions

  Posted by Barnsite, 12 June 2018 · 155 views

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):

SELECT 
	c.Path,
	c.Name
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.



Source

  • 0



My Picture

1 user(s) viewing

0 members, 1 guests, 0 anonymous users

Categories

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download