0

I've been tasked with handling import jobs in to SQL server based on various time zones. Files arrive on a Windows Server from multiple regions for example Brazil, Singapore, Australia, various parts of the U.S. and also Europe.

Each file will be imported in to SQL tables by multiple stored procedures. Each stored procedure needs to be executed based on a scheduled time according to the time zone related to the origin of the file.

Working from a set time is proving tricky due to the fact that each region adjusts for day light saving at different times of the year. Say for example the UK moves it's clock forward for day light saving, Brazil may not move their time forward for another 3 weeks (don't quote me on that, I've used those times only for example purposes).

My question is; how can I schedule jobs to run on the same server based on multiple time zones?

I can see this may be possible if I were to create a timezone lookup table in SQL which shows the relationship between each time zone at each stage of the year but this seems quite cumbersome and will also take a considerable amount of time to populate the table.

Windows scheduler seems to use the date/time settings of the local server and although it does adjust for daylight saving, this will only be appropriate for one region. Has anyone had to handle this in SQL Server before? Or can anyone recommend a scheduling tool external to SQL Server that can initiate tasks based on different time zones?

Any help or advice would be greatly appreciated.

PeteFoulkes
  • 111
  • 10
  • Look here maybe it willhelp you [link](http://stackoverflow.com/questions/10398003/sql-server-jobs-schedules-us-vs-uk-daylight-savings-adjustment) – The Reason May 31 '15 at 16:49
  • [Prepare yourself mentally. You will need it. Trust me, I've been there before.](https://youtu.be/-5wpm-gesOY0) – Zohar Peled May 31 '15 at 20:07

1 Answers1

0

You won't be able to transparently and easy configure a single instance of SQL Server to run several sets of tasks in different timezones, by definition (the instance is single, all sets of tasks will be in the same timezone).

You are, however, able to write your own script in any language you like (for example, CLR .NET extension for MSSQL or just plain Transact-SQL), which is configured to to the following:

  1. Iterate over the list of each region you want the task to be run
  2. Convert the time of the region to server time and set the action to be executed (via sp_schedule for example).
  3. Repeat the next period.

This task should of course be run at +12 UTC, thus definitely making it execute first on that date (as the time conveniently starts in Japan).

Implementing it this way would be pretty clear and reliable regardless of daylight savings, timezone updates and everything. Just make sure to keep the configuration of your partners timezones up to date.

Samvel Avanesov
  • 422
  • 2
  • 7