1. List all reporting service subscription jobs.
USE ReportServer
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,'exec msdb.dbo.sp_update_job @job_name = '''+CAST(Schedule.ScheduleID AS VARCHAR(max))+''',@enabled = 0' AS sqlcmd
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
2. Disable each jobs.
exec msdb.dbo.sp_update_job @job_name = 'A83AE658-E203-457B-83D3-5715EA64A47A',@enabled = 0
USE ReportServer
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,'exec msdb.dbo.sp_update_job @job_name = '''+CAST(Schedule.ScheduleID AS VARCHAR(max))+''',@enabled = 0' AS sqlcmd
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
2. Disable each jobs.
exec msdb.dbo.sp_update_job @job_name = 'A83AE658-E203-457B-83D3-5715EA64A47A',@enabled = 0
No comments:
Post a Comment