In an earlier article, Solve Time Zone, GMT, and UTC problems using the T-SQL Toolbox database, I described T-SQL Toolbox, a free, open-source SQL Server database that handles time zone, date, and time calculations in a clean and efficient way. Available here at the CodePlex Archive, and here at GitLab, T-SQL Toolbox relies on time zone and time zone adjustment data in its two tables for many of its own calculations. However, T-SQL Toolbox does not update that data. I built primitive C-Sharp and VB.net applications that extract the latest time zone and time zone adjustment data from the Windows registry, and I discussed those applications in that earlier SQL Shack article. In the article, I explained that we can build SQL Server UPDATE statements with this extracted data, and then update the T-SQL Toolbox tables. This article describes a better approach and a better solution.
For this article, I used a SQL Server 2014 Standard Edition environment on an updated Windows 10 PC. This article will explore a C-Sharp EXE console application that gathers the relevant data for the table updates. The article also describes a VB.net EXE console application that operates the same way. I built both of these products with Visual Studio Enterprise 2015, and I made both applications available on my GitHub page. The solution itself involves the following two components:
- The Windows console application EXE file that builds one text file with time zone data, and one text file with time zone adjustment data
- A SQL Server stored procedure that executes the Windows EXE, and then BULK INSERTs from those text files into the T-SQL Toolbox database tables
We will examine each of them individually in this article.
As stated in my earlier article, Microsoft engineer Matt Johnson-Pint handles Windows time zone engineering. At his blog, he wrote that time zones constantly change. As our goal, we want to update the TSqlToolbox database tables(as shown below) to reflect those changes.
Mr. Johnson-Pint explained here and here that as part of the Windows update process, an updated Windows PC will have all the time zone and time zone adjustment data we’ll need, through Windows registry updates. According to one source, SQL Server has extended SQL Server stored procedures that can directly extract the data from the registry. Although these stored procedures look tempting, we can’t use them because Microsoft does not support them, and could scrap them at any time. As a result, we need another approach.
The Windows Console Application EXE
I first built a C-Sharp EXE console application, and then a cloned VB.net EXE console application, that themselves build text files with the time zone and time zone adjustment data we’ll need. I placed the EXE’s in the C:\TSQL_TOOLBOX_UPDATE directory on the Windows device hosting the SQL Server environment before I ran them. Either one of these EXE files will support the T-SQL Toolbox update process.
The EXE’s query the Windows registry for the time zone data, and place the data in the below text files that will have compatibility with the target database table structures.
The EXE’s save the files in the below directory, and they build that directory if needed.
They will overwrite the text files in that directory if those files already exist.
Both EXE’s use nested “for each” loops for their main engineering. In its outer For-Each loop, each EXE extracts data for all time zones available in the registry. The outer For-Each loop in each EXE sets an Id value for each time zone. The inner For-Each loop of each EXE can see this Id value. The inner For-Each loops extract data from the registry for all time zone adjustments tied to the time zone Id value of the outer For-Each loop. In this CSharp_Build_TimeZone_Info_Files screenshot, line 143 places the “parent” time zone ID value in the time zone adjustment row:
In this VBNet_Build_TimeZone_Info_Files screenshot, line 103 places the “parent” time zone ID value in the time zone adjustment row:
In these applications, the inner For-Each loops use the parent timeZoneIDVal values described above as the second value, as they build each TIMEZONEADJUSTMENTFILE.txt file row. For the T-SQL Toolbox table updates, this ties each TIMEZONEADJUSTMENTFILE.txt row as a child row to one TIMEZONEFILE.txt file row, which itself operates as a parent row.
The text files use the pipe symbol | as the field delimiter and Environment.Newline as the row delimiter. The data import process described later in this article handles Environment.Newline as a character. Each EXE file builds these text data files and handles the relevant directories, the same way.
The Stored Procedure
If we place either one of these EXE files in the directory as described above, we can call that EXE from a T-SQL Toolbox stored procedure, with the xp_cmdshell extended stored procedure. We can use xp_cmdshell to run a Windows command-line statement. Full disclosure: at least one source states that Microsoft will deprecate extended stored procedures, including xp_cmdshell. However, xp_cmdshell still works – at least for SQL Server 2014 – so we’ll use it here. The stored procedure below.
CREATE PROCEDURE [DateTimeUtil].[UPDATE_TSqlToolbox_Tables]
-- Enable xp_cmdshell, run the EXE, and immediately disable xp_cmdshell
EXEC sp_configure 'show advanced options', 1
EXEC sp_configure 'xp_cmdshell', 1
EXEC master..xp_cmdshell '"C:\TSQL_TOOLBOX_UPDATE\VBNet_Build_TimeZone_Info_Files.exe"'
EXEC sp_configure 'xp_cmdshell', 0
EXEC sp_configure 'show advanced options', 0
-- Remove all rows from the TimeZoneAdjustmentRule
-- table first, as the child table rows. Then remove
-- the TimeZone table rows as the parent rows.
DELETE FROM DateTimeUtil.TimeZoneAdjustmentRule
DELETE FROM DateTimeUtil.TimeZone
-- Insert from the TIMEZONE file into the
-- TimeZone table as the parent table
BULK INSERT DateTimeUtil.TimeZone
-- Insert from the TIMEZONEADJUSTMENTFILE file into the
-- TimeZoneAdjustmentRule table as the child table
BULK INSERT DateTimeUtil.TimeZoneAdjustmentRule
This procedure first enables xp_cmdshell, with the calls to sp_configure at lines 7 through 11. Then, it finds and runs the EXE, in this case, VBNet_Build_TimeZone_Info_Files.exe in the directory C:\TSQL_TOOLBOX_UPDATE as a hard-coded path.
The EXE file CSharp_Build_TimeZone_Info_Files.exe would also work at this stored procedure location. The called EXE builds the below text files:
And places those files in the C:\TSQL_TOOLBOX_UPDATE\TEXT_FILES directory, also as a hard-coded path. As explained above, both EXE’s will overwrite any existing time zone data files in this directory and will build the directory if necessary. Next, the [DateTimeUtil].[UPDATE_TSqlToolbox_Tables] Stored Procedure disables xp_cmdshell, with the calls to sp_configure at lines 15 through 19. To help preserve security, SQL Server disables xp_cmdshell by default. As seen here, the UPDATE_TSqlToolbox_Tables stored procedure enables xp_cmdshell long enough to run the EXE we want, and then immediately disables it. To use the C-Sharp EXE, change the EXE file name at line 13, and proceed.
At lines 25 and 26, the stored procedure deletes all rows from the tables. This screenshot shows the basic parent-child relationship between the tables:
The stored procedure deletes from the tables in this order because of the primary key/foreign key, or parent-child, the relationship between those tables. These two screenshots show more details about that relationship:
This relationship requires row deletion from the child table [DateTimeUtil].[TimezoneAdjustmentRule] first, then from the [DateTimeUtil].[Timezone] parent table.
At line 31, the BULK INSERT statement inserts rows from the TIMEZONEFILE.txt file into the parent [DateTimeUtil].[Timezone] table.
At line 42, the BULK INSERT statement inserts rows from the TIMEZONEFILEADJUSTMENTFILE.txt file into the child[DateTimeUtil].[TimezoneAdjustmentRule] table.
The stored procedure runs these BULK INSERTs in this order, need to insert rows into the parent table first, and then the child table. At this point, the T-SQL Toolbox database has its updated rows.
Configuration, Preparation, and Security Considerations
First, run Windows Update on the device hosting the SQL Server environment, that itself hosts the TSqlToolbox database. This will update the Windows registry on that device with the latest time zone and time zone adjustment data.
Second, place at least one of the EXE’s described above in a specified directory. This article assumes the C:\TSQL_TOOLBOX_UPDATE directory, but any directory will work, as long as the server with the TSqlToolbox database can see it. CREATE the [DateTimeUtil].[UPDATE_TSqlToolbox_Tables] Stored procedure in the T-SQL Toolbox database, and make sure that the C:\TSQL_TOOLBOX_UPDATE directory-path at lines 13, 32, and 43 in the stored procedure matches the actual directory path described above.
Also, make sure that the directory path at line 51 in the C-Sharp EXE file, and the directory path at line 10 in the VB.net EXE file, all match this designated directory path. We can certainly change the path, but that change must happen in a consistent, synchronized way across Windows, the stored procedure, and the EXE files, for this solution to work. This screenshot shows the relevant directory path and file name for the EXE in the UPDATE_TSqlToolbox_Tables stored procedure, at line 13:
This screenshot shows the C:\TSQL_TOOLBOX_UPDATE directory-path at lines 32 and 43 in the UPDATE_TSqlToolbox_Tables:
This screenshot shows the relevant directory path and file name in the Module1.vb of the VB.net application VBNet_Build_TimeZone_Info_Files, at line 10:
This screenshot shows the relevant directory path and file name in the Program.cs of the C-Sharp application Sharp_Build_TimeZone_Info_Files, at line 51:
Third, run the [DateTimeUtil].[UPDATE_TSqlToolbox_Tables] Stored procedure. This completes the T-SQL Toolbox database table update process.
Because of legal, corporate, business, etc. policies, we might not want to run this solution in a live production environment. In this situation, place the solution software on an updated, air-gapped Windows device hosting a SQL Server version/edition that matches the production environment. Install and run the TSqlToolbox database and the software described in this article. Proceed with steps one through three, as described above. After a security review of the new table rows, copy the data between the devices with a database backup, insert statements, etc.
Differences between the Original and Updated data
Earlier, this article discussed C-Sharp and VB.net applications that build the raw text data files. Although these applications have the same overall logic and behavior, testing shows that they build time zone adjustment data files with subtle differences compared to the original T-SQL Toolbox data.
The steps described here will set up the testing:
- Use the original TSqlToolbox creation script, as described in the first paragraph of this article, to build a clone database called TSqlToolbox_backup
- Run the C-Sharp or VB.net console applications, to build the text files
- Run the UPDATE_TSqlToolbox_Tables stored procedure, to update the T-SQL Toolbox tables
Finally, run this script to compare the common rows in the original and updated time zone adjustment tables:123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118-- Build table variables to hold rows from the "original"-- and "new" DateTimeUtil.TimezoneAdjustmentRule tables.-- Include the Timezone.Identifier column.DECLARE @TimeZoneAdjustmentRuleBackup Table([TimeZoneIdentifier] [nvarchar](100),[Id] [int] NOT NULL,[TimezoneId] [int] NULL,[RuleNo] [int] NULL,[DateStart] [datetime2] NULL,[DateEnd] [datetime2] NULL,[DaylightTransitionStartIsFixedDateRule] [bit] NULL,[DaylightTransitionStartMonth] [int] NULL,[DaylightTransitionStartDay] [int] NULL,[DaylightTransitionStartWeek] [int] NULL,[DaylightTransitionStartDayOfWeek] [int] NULL,[DaylightTransitionStartTimeOfDay] [time](7) NULL,[DaylightTransitionEndIsFixedDateRule] [bit] NULL,[DaylightTransitionEndMonth] [int] NULL,[DaylightTransitionEndDay] [int] NULL,[DaylightTransitionEndWeek] [int] NULL,[DaylightTransitionEndDayOfWeek] [int] NULL,[DaylightTransitionEndTimeOfDay] [time](7) NULL,[DaylightDeltaSec] [int] NULL)INSERT INTO @TimeZoneAdjustmentRuleBackupSELECT TZB.Identifier, TZARB.*FROM TSqlToolbox_backup.DateTimeUtil.TimezoneAdjustmentRule TZARB INNER JOINTSqlToolbox_backup.DateTimeUtil.Timezone TZB ONTZB.Id = TZARB.TimezoneIdORDER BY TZB.Identifier--SELECT * FROM @TimeZoneAdjustmentRuleBackupDECLARE @TimeZoneAdjustmentRule Table([TimeZoneIdentifier] [nvarchar](100),[Id] [int] NOT NULL,[TimezoneId] [int] NULL,[RuleNo] [int] NULL,[DateStart] [datetime2] NULL,[DateEnd] [datetime2] NULL,[DaylightTransitionStartIsFixedDateRule] [bit] NULL,[DaylightTransitionStartMonth] [int] NULL,[DaylightTransitionStartDay] [int] NULL,[DaylightTransitionStartWeek] [int] NULL,[DaylightTransitionStartDayOfWeek] [int] NULL,[DaylightTransitionStartTimeOfDay] [time](7) NULL,[DaylightTransitionEndIsFixedDateRule] [bit] NULL,[DaylightTransitionEndMonth] [int] NULL,[DaylightTransitionEndDay] [int] NULL,[DaylightTransitionEndWeek] [int] NULL,[DaylightTransitionEndDayOfWeek] [int] NULL,[DaylightTransitionEndTimeOfDay] [time](7) NULL,[DaylightDeltaSec] [int] NULL)INSERT INTO @TimeZoneAdjustmentRuleSELECT TZ.Identifier, TZRB.*FROM TSqlToolbox.DateTimeUtil.TimezoneAdjustmentRule TZRB INNER JOINTSqlToolbox.DateTimeUtil.Timezone TZ ONTZ.Id = TZRB.TimezoneIdORDER BY TZ.Identifier--SELECT * FROM @TimeZoneAdjustmentRuleSELECT TZAR.TimeZoneIdentifier, TZAR.RuleNo, TZARB.TimeZoneIdentifier AS 'Backup TimeZoneIdentifier', TZARB.RuleNo AS 'Backup RuleNo',TZAR.DateStart AS 'TZ DATESTART', TZARB.DateStart AS 'BACKUP TZ ADJUSTMENT TABLE DATESTART',TZAR.DateEnd AS 'TZ DATEEND', TZARB.DateEnd AS 'BACKUP TZ ADJUSTMENT TABLE DATEEND',TZAR.DaylightTransitionStartIsFixedDateRule AS 'TZ DAYLIGHTTRANSITIONSTARTISFIXEDDATERULE',TZARB.DaylightTransitionStartIsFixedDateRule AS 'BACKUP TZ ADJUSTMENT TABLE DAYLIGHTTRANSITIONSTARTISFIXEDDATERULE',TZAR.DaylightTransitionStartMonth AS 'TZ DAYLIGHTTRANSITIONSTARTMONTH',TZARB.DaylightTransitionStartMonth AS 'BACKUP TZ ADJUSTMENT TABLE DAYLIGHTTRANSITIONSTARTMONTH',TZAR.DaylightTransitionStartDay AS 'TZ DAYLIGHTTRANSITIONSTARTDAY',TZARB.DaylightTransitionStartDay AS 'BACKUP TZ ADJUSTMENT TABLE DAYLIGHTTRANSITIONSTARTDAY',TZAR.DaylightTransitionStartWeek AS 'TZ DAYLIGHTTRANSITIONSTARTWEEK',TZARB.DaylightTransitionStartWeek AS 'BACKUP TZ ADJUSTMENT TABLE DAYLIGHTTRANSITIONSTARTWEEK',TZAR.DaylightTransitionStartDayOfWeek AS 'TZ DAYLIGHTTRANSITIONSTARTDAYOFWEEK',TZARB.DaylightTransitionStartDayOfWeek AS 'BACKUP TZ ADJUSTMENT TABLE DAYLIGHTTRANSITIONSTARTDAYOFWEEK',TZAR.DaylightTransitionStartTimeOfDay AS 'TZ DAYLIGHTTRANSITIONSTARTTIMEOFDAY',TZARB.DaylightTransitionStartTimeOfDay AS 'BACKUP TZ ADJUSTMENT TABLE DAYLIGHTTRANSITIONSTARTTIMEOFDAY',TZAR.DaylightTransitionEndIsFixedDateRule AS 'TZ DAYLIGHTTRANSITIONENDISFIXEDDATERULE',TZARB.DaylightTransitionEndIsFixedDateRule AS 'BACKUP TZ ADJUSTMENT TABLE DAYLIGHTTRANSITIONENDISFIXEDDATERULE',TZAR.DaylightTransitionEndMonth AS 'TZ DAYLIGHTTRANSITIONENDMONTH',TZARB.DaylightTransitionEndMonth AS 'BACKUP TZ ADJUSTMENT TABLE DAYLIGHTTRANSITIONENDMONTH',TZAR.DaylightTransitionEndDay AS 'TZ DAYLIGHTTRANSITIONENDDAY',TZARB.DaylightTransitionEndDay AS 'BACKUP TZ ADJUSTMENT TABLE DAYLIGHTTRANSITIONENDDAY',TZAR.DaylightTransitionEndWeek AS 'TZ DAYLIGHTTRANSITIONENDWEEK',TZARB.DaylightTransitionEndWeek AS 'BACKUP TZ ADJUSTMENT TABLE DAYLIGHTTRANSITIONENDWEEK',TZAR.DaylightTransitionEndDayOfWeek AS 'TZ DAYLIGHTTRANSITIONENDDAYOFWEEK',TZARB.DaylightTransitionEndDayOfWeek AS 'BACKUP TZ ADJUSTMENT TABLE DAYLIGHTTRANSITIONENDDAYOFWEEK',TZAR.DaylightTransitionEndTimeOfDay AS 'TZ DAYLIGHTTRANSITIONENDTIMEOFDAY',TZARB.DaylightTransitionEndTimeOfDay AS 'BACKUP TZ ADJUSTMENT TABLE DAYLIGHTTRANSITIONENDTIMEOFDAY',TZAR.DaylightDeltaSec AS 'TZ DAYLIGHTDELTASEC',TZARB.DaylightDeltaSec AS BACKUP TZ ADJUSTMENT TABLE DAYLIGHTDELTASEC'FROM @TimeZoneAdjustmentRule TZAR INNER JOIN@TimeZoneAdjustmentRuleBackup TZARB ON TZAR.TimeZoneIdentifier = TZARB.TimeZoneIdentifier ANDTZAR.RuleNo = TZARB.RuleNoWHERE TZAR.DateEnd <> TZARB.DateEnd ORTZAR.DaylightTransitionStartIsFixedDateRule <> TZARB.DaylightTransitionStartIsFixedDateRule ORTZAR.DaylightTransitionStartMonth <> TZARB.DaylightTransitionStartMonth ORTZAR.DaylightTransitionStartDay <> TZARB.DaylightTransitionStartDay ORTZAR.DaylightTransitionStartWeek <> TZARB.DaylightTransitionStartWeek ORTZAR.DaylightTransitionStartDayOfWeek <> TZARB.DaylightTransitionStartDayOfWeek ORTZAR.DaylightTransitionStartTimeOfDay <> TZARB.DaylightTransitionStartTimeOfDay ORTZAR.DaylightTransitionEndIsFixedDateRule <> TZARB.DaylightTransitionEndIsFixedDateRule ORTZAR.DaylightTransitionEndMonth <> TZARB.DaylightTransitionEndMonth ORTZAR.DaylightTransitionEndDay <> TZARB.DaylightTransitionEndDay ORTZAR.DaylightTransitionEndWeek <> TZARB.DaylightTransitionEndWeek ORTZAR.DaylightTransitionEndDayOfWeek <> TZARB.DaylightTransitionEndDayOfWeek ORTZAR.DaylightTransitionEndTimeOfDay <> TZARB.DaylightTransitionEndTimeOfDayORDER BY TZAR.DaylightTransitionStartTimeOfDay
This script first builds table variable @TimeZoneAdjustmentRule, with the updated TimezoneAdjustmentRule table rows from the TSqlToolbox database, and table variable @TimeZoneAdjustmentRuleBackup, with TimezoneAdjustmentRule rows from the TSqlToolbox_backup database. These table variables include the Timezone.Identifier column. The last SQL query inner joins the table variables on time zone identifiers and time zone adjustment rule numbers. An inner join avoids the new rows of the updated data set. The query compares the matching column pairs between the table variables, returning a row if it sees a difference in at least one pair. Testing showed differences in 150 time zone adjustment rows. This screenshot shows some examples:
Other column pairs in other rows also show differences. The differences seem random and “minor,” they happen in other column pairs, and they could reflect issues with the original data, the updated data, or both. These differences could become important for applications that use the data, so keep them in mind.
The T-SQL Toolbox database solves tricky time and date calculation problems. Now, we can easily update its tables to reflect the latest time zone and time zone adjustment changes, based on the latest updates from Microsoft.