Updating schedules for end of Daylight Saving Time

  • 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

  • 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
  • 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

  • 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
  • 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

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

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