October 28, 2016 at 5:43 am
Hi there
I have a set of reports which are scheduled to be sent out at 0930 every Monday morning.
I have been asked to change the time from 0930 to 0830.
However there are around 90 of these reports to update.
Is there a way through a t-sql script of identifying and updating the times of these schedules for the
reports in the Report Server database?
October 28, 2016 at 2:18 pm
I'm assuming they have individual schedules? If they use shared schedules then you can update them from Management Studio.
The query at the end will output the Report name, SSRS Schedule id and SQL Agent Job name (which will be the same), the SQL agent job ID and SQL Agent Schedule id of reports scheduled at 9:30am.. (It uses the next run date from the SQL Agent job schedule as assumes that the jobs only run once a day.
Using this output you could then script something to update the SQL Agent schedule using sp_update_schedule
e.g.
EXEC msdb..sp_update_schedule
@schedule_id = ??
,@active_start_time = 083000
You would then need to update the start time in the dbo.Schedule table in the ReportingServices database otherwise if someone edits the subscription using the SSRS webpage then it could revert back to the 9:30 time. Note you will need to disable the update trigger on this table while you do it.
This is totally unsupported so if you are going to do it this way ensure you test before hand. If it was me, i would set up a shared schedule and then change the jobs manually to use this. This makes it a lot easier to handle other changes in the future.
SELECT ReportName = c.Name
, SSRSScheduleID = s.ScheduleID
, SQLAgentJobName = sj.name
, SQLAgentJobID = sj.job_id
, SQLAgentScheduleId = sjs.schedule_id
FROM ReportSchedule rs
JOIN Schedule s
ON rs.ScheduleID = s.ScheduleID
JOIN Catalog c
ON rs.ReportID = c.ItemID
JOIN msdb.dbo.sysjobs sj
ON CAST(s.ScheduleID AS sysname) = sj.name
JOIN msdb.dbo.sysjobschedules sjs
ON sjs.job_id = sj.job_id
WHERE sjs.next_run_time = CONVERT(INT, REPLACE(CONVERT(VARCHAR(8), '09:30:00', 108),':',''));
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply