How to temporarily stop SSRS subscriptions

Who is online?  0 guests and 0 members
Home  »  Forums   »  microsoft business intelligence   »  reporting services   » How to temporarily stop SSRS subscriptions

How to temporarily stop SSRS subscriptions

Topic RSS Feed

Posts under the topic: How to temporarily stop SSRS subscriptions

Posted: 1/12/2012

Jedi Youngling 24  points  Jedi Youngling
  • Joined on: 1/12/2012
  • Posts: 7

I sometimes will be needing to temporarily stop scheduled subscriptions due to a review process of the reports.  Am I correct in thinking that I will be able to use the following to safely stop the subscription temporarily without reporting server throwing errors if the subscription tries to run when the sql server agent job is stopped?

SELECT     Schedule.ScheduleID AS SQLAgent_Job_Name, Subscriptions.Description AS sub_desc, Subscriptions.DeliveryExtension AS sub_delExt, 
[Catalog].Name AS ReportName, [Catalog].Path AS ReportPath,u.UserName,subscriptions.lastruntime,subscriptions.LastStatus
FROM ReportSchedule INNER JOIN
Schedule ON ReportSchedule.ScheduleID = Schedule.ScheduleID INNER JOIN
Subscriptions ON ReportSchedule.SubscriptionID = Subscriptions.SubscriptionID INNER JOIN
[Catalog] ON ReportSchedule.ReportID = [Catalog].ItemID AND Subscriptions.Report_OID = [Catalog].ItemID
inner join Users u on u.UserID=schedule.CreatedById
where [Catalog].Name='Name of report'

 

USE msdb
EXEC sp_stop_job @job_name = 'Schedule.ScheduleID name from query'

Posted: 1/12/2012

Padawan 1392  points  Padawan
  • Joined on: 1/3/2010
  • Posts: 250

Hi lculley.

yes, if you do not want to interfere the reporting service, that is the same approach I do.

Take a look into my blog post. If you want, there is also my video available.


Posted: 1/12/2012

Jedi Youngling 8  points  Jedi Youngling
  • Joined on: 8/19/2010
  • Posts: 4

A GUI option is that once you find the job related to the subscription, you can just uncheck the 'enabled' checkbox in the job properties.

Job Properties

 

 


Page 1 of 1 (3 items)