Posted: 1/12/2012
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.LastStatusFROM 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'
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.
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.