Frank Solomon
Setting the target Directory

How to update the T-SQL Toolbox database

April 6, 2020 by

Introduction

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.

Goal

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.

The T-SQL Toolbox database tables as seen in the Object Explorer.

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 CSharp_Build_TimeZoneInfo_Files and VBNet_Build_TimeZoneInfo_Files application EXE files in the C:\TSQL_TOOLBOX_UPDATE directory.

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.

TIMEZONEFILE.txt
TIMEZONEADJUSTMENTFILE.txt

The EXE’s save the files in the below directory, and they build that directory if needed.

C:\TSQL_TOOLBOX_UPDATE\TEXT_FILES

The CSharp_Build_TimeZoneInfo_Files and VBNet_Build_TimeZoneInfo_Files application EXE files and the TEXT_FILES subdirectory in the C:\TSQL_TOOLBOX_UPDATE directory.

They will overwrite the text files in that directory if those files already exist.

The timeZoneAdjustmentFile and timeZoneFile text files in the C:\TSQL_TOOLBOX_UPDATE\TEXT_FILES directory have the data for the T-SQL Toolbox database update.

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:

The timeZoneIDVal variable used in the inner foreach loop, as seen in the Sharp_Build_TimeZone_Info_Files application at line 143.

In this VBNet_Build_TimeZone_Info_Files screenshot, line 103 places the “parent” time zone ID value in the time zone adjustment row:

The timeZoneIDVal variable used in the inner For Each loop, as seen in the VBNet_Build_TimeZone_Info_Files application at line 103.

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.

The [DateTimeUtil].[UPDATE_TSqlToolbox_Tables] stored procedure.

The [DateTimeUtil].[UPDATE_TSqlToolbox_Tables] stored procedure, placed in the T-SQL Toolbox database.

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:

TIMEZONEFILE.txt
TIMEZONEADJUSTMENTFILE.txt

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:

[DateTimeUtil].[TimezoneAdjustmentRule]
[DateTimeUtil].[Timezone]

This database diagram shows the parent-child relationship for the T-SQL Toolbox Timezone and TimezoneAdjustmentRule 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:

Properties of the parent-child relationship for the T-SQL Toolbox Timezone and TimezoneAdjustmentRule tables.

Tables and columns of the parent-child relationship for the Timezone and TimezoneAdjustmentRule tables.

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:

The absolute directory path and file name for the VBNet_Build_TimeZone_Info_Files EXE called in the T-SQL Toolbox database 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:

In stored procedure, UPDATE_TSqlToolbox_Tables, the absolute directory path and file name for the TIMEZONEFILE.

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:

Setting the targetDirectory

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:

Setting the target Directory

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:

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:

Comparing the T-SQL Toolbox table values before and after the updates.

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.

Conclusion

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.

Frank Solomon
Latest posts by Frank Solomon (see all)

Related posts:

  1. SQL Server Replication with a table with more than 246 columns
  2. Solve Time Zone, GMT, and UTC problems using the T-SQL Toolbox database
  3. SQL Database Backups using PowerShell Module – DBATools
  4. SQL interview questions on database backups, restores and recovery – Part II
  5. SQL interview questions on database backups, restores and recovery – Part I
168 Views