Skip to content

Instantly share code, notes, and snippets.

@cporteou
Created October 22, 2017 11:06
Show Gist options
  • Star 1 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save cporteou/374968468e3ca475dfed8136cdabac6e to your computer and use it in GitHub Desktop.
Save cporteou/374968468e3ca475dfed8136cdabac6e to your computer and use it in GitHub Desktop.
This returns a list of all subscriptions in an instance, their SQL Agent Job IDs and built commands for Starting, enabling & disabling jobs.
USE ReportServer
SELECT
cat.Name,
cat.Path,
sch.ScheduleID AS AgentJobID,
sch.LastRunTime,
'EXEC msdb.dbo.sp_start_job N''' + CAST(sch.ScheduleID as nvarchar(36)) + ''' ;' AS StartJob,
'EXEC msdb.dbo.sp_update_job @job_name = N' + CAST(sch.ScheduleID as nvarchar(36)) + ', @enabled = 1 ;' AS EnableJob,
'EXEC msdb.dbo.sp_update_job @job_name = N' + CAST(sch.ScheduleID as nvarchar(36)) + ', @enabled = 0 ;' AS DisableJob
FROM
dbo.Schedule sch
INNER JOIN
dbo.ReportSchedule rsch
ON sch.ScheduleID = rsch.ScheduleID
INNER JOIN
dbo.Catalog cat
ON rsch.ReportID = cat.ItemID
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment