Updating schedules for end of Daylight Saving Time

Question

Post reply

Updating schedules for end of Daylight Saving Time

Scott Arendt

SSCrazy Eights

Points: 8218
More actions
October 23, 2008 at 3:05 pm

#200346

I have a number of reports that customers receive each day. With Daylight Saving Time ending shortly, I thought that maybe this year I would automate a process for updating schedule times so that the reports still arrive for the customer at the same local time each day.
From my research, it looks like my best option is to utilize sp_update_schedule.
In pseudo code, I think that I am looking at something like this:
EXEC msdb.dbo.sp_update_schedule @name = 'Customer Report Schedule', @active_start_time = prev_start_time + 10000
However, I can't seem to find any examples where someone has tried this. I'm just looking for input or suggestions.
Thanks,
Scott

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Jerry Hung SSChampion Points: 12968 More actions · Answer 1

I have a question

If a subscriber wants a report at 9 AM

won't it still be 9 AM after the DST change?

The SQL time changes with the OS time change

SQLServerNewbieMCITP: Database Administrator SQL Server 2005

Scott Arendt SSCrazy Eights Points: 8218 More actions · Answer 2

That's a good question and I should have noted that the server operates on UTC. So, if I don't do anything, the report that a customer currently gets at 9 AM will start arriving at 8 AM.

Scott

Jerry Hung SSChampion Points: 12968 More actions · Answer 3

Yes, the lovely UTC vs Daylight Savings Time

Looking around the sp_update_schedule Books Online

http://msdn.microsoft.com/en-us/library/ms187354(SQL.90).aspx

I am thinking 3 options

1. the way you did, update schedule

2. Create all the jobs with new schedule, and Disable the current jobs

(so you have 2 sets of jobs, one on DST time, one not)

3. Attach all jobs to a "shared schedule" instead of 1 schedule per job

(so in each job, you Pick a schedule from the buttom "Pick" instead of creating a new schedule)

Then I believe you only need to change that shared schedule's time instead of every job

I prefer Option 3

sp_help_schedule should show all the schedules

Don't have SSMS with me with the moment, otherwise I would try it out myself first

SQLServerNewbieMCITP: Database Administrator SQL Server 2005

Scott Arendt SSCrazy Eights Points: 8218 More actions · Answer 4

Jerry Hung (10/23/2008)
3. Attach all jobs to a "shared schedule" instead of 1 schedule per job
(so in each job, you Pick a schedule from the buttom "Pick" instead of creating a new schedule)
Then I believe you only need to change that shared schedule's time instead of every job

Interestingly enough, option 3 is the way that our development group is moving, but I need to support the legacy stuff for now. Option 2 is something that I had not considered.

I'm still hoping someone can confirm the code, it just seems odd to add 10000 to an integer to change the time by an hour ...

Thanks,

Scott