Dietz


Hi There

I need to get rid of all subscriptions (including cleaning up all associated sql jobs) for RS 2000 and 2005.

We have hundreds of reports , going through them 1 by 1 is not an option.

When i look at the system tables, i join Subscription to Catalog, but i find reports with subscriptions in the system tables but when i go to Report Manger there is no subscription for the report.

In a nutshell , how can i see all valid subscription for all reports in RS, how can i delete all of them and make sur eall sql jobs associated etc are deleted.

Thanx




Re: How do i find all subscriptions

Ronny Lewandowski


Hi Dietz, try it with the object-model of RS:

rs = new rs2005.ReportingService2005();

rs.Credentials = System.Net.CredentialCache.DefaultCredentials;

rs.Url = "http://<Server>/ReportServer/ReportService2005.asmx";

Get all Subscriptions from a related Report

string RelatedReport = ConfigurationManager.AppSettings["RelatedReport"];

rs2005.Subscription[] subscr = rs.ListSubscriptions(RelatedReport, "");

string[] SubscID = new string[subscr.Length];

int icount = 0;

foreach (rs2005.Subscription s in subscr)

{

SubscID[icount] = s.SubscriptionID;

icount++;

}

Delete all Subscriptions from this Report.

for (int j = 0; j <= SubscID.Length-1; j++)

{

rs.DeleteSubscription(SubscID[j]);

}

If you want to delete just all Subscriptions, please read following Remarks from Microsoft:

You can supply a null (Nothing in Visual Basic) value for the Owner and Report parameters. The information that the ListSubscriptions method returns varies depending on the parameters that are submitted:

  • If the values of both Owner and Report are null, the method returns all subscriptions for all reports that the current user has permission to view.

  • If only the Owner parameter is submitted, the method returns all subscriptions for all reports that the specified user has created and has permission to view.

  • If only the Report parameter is submitted, the method returns all subscriptions for all users of the specified report that the current user has permission to view.

  • If valid values are supplied for both the Owner parameter and Report parameter, the method returns all subscriptions for the specified report that the specified user created and has permission to view.

Hope this helps, all the Best, Ronny






Re: How do i find all subscriptions

Dietz

Hi Ronny

Thank You for the feedback, unfortunately i am tsql guy. Does anyone know of system stored procs that can give me this info, or delete subscriptions for RS

Or a tsql equivelant of the code above

Thanx






Re: How do i find all subscriptions

Ronny Lewandowski

Build a cursor to iterate over all Reports (Catalog)

Then set up a delete statement in interation

Delete from Subscriptions where Report_OID=Catalog.ItemID;

All Subscriptions will be deleted and also the jobs, beacause the triggers are doing their job.

Or use sp DeleteActiveSubscriptions, DeleteSubscription

Ronny





Re: How do i find all subscriptions

Dietz

Awesome, thanx Ronny i will try this.



Re: How do i find all subscriptions

Lukasz Pawlowski -- MS

Sorry - we don't support folks directly accessing the report server database. If you build a solution on top of database calls, you will find that with service packs/hotfixes, or major versions, your solution will eventually break.

I have a blog post for how to monitor subscriptions here:

http://blogs.msdn.com/lukaszp/archive/2005/12/30/508328.aspx

Thanks,

-Lukasz






Re: How do i find all subscriptions

Ronny Lewandowski

Hi Lukasz, thank you for not supporting me!

i just want to let you know that accessing the Report Server Database directly is a job on admins. As a admin i am responsible for service packs / hofixes or major versions and i am able to read the release-notes. I also remember that i wrote a little tool which supports me in deleteing subscriptions. I can have a short look to see if the schema changes.

btw: arent the SOAP-API calls using the same stored procedures to acces the database

All the Best

Ronny





Re: How do i find all subscriptions

Lukasz Pawlowski -- MS

The SOAP APIs to use various stored procs. However, the definition of stored procs can change over time and unexpectedly. As such, I have to empahsize that you shouldn't rely on them.

The difference is that we version the SOAP APIs to ensure backwards compatibility. For stored procs/tables/views/etc, we just change them as needed to support our development efforts.

-Lukasz






Re: How do i find all subscriptions

Ronny Lewandowski

Hi Lukasz,

in general i agree with your statements, but until the Reporting Services are having some gaps in Administration i'm forced to do so.

And it was not so bad, because we earned money to sell those kind of hacks even to global players.

All the Best

Ronny





Re: How do i find all subscriptions

Lukasz Pawlowski -- MS

Ronny,

Thanks for your honest feedback - I will communicate this more broadly in the Reporting Services team.

Thanks,

-Lukasz