Start shared HTA with PowerShell

I have some tools made as HTML Applications (HTAs). And I am not the only one to use such a tool, but HTAs runs locally and still the tool must be updated for all users.
A solution is to have a launch script. In my case it is made in PowerShell. The launch script is called from a shortcut, that the user can either use from a fileshare or copy to a local place like the Windows Desktop.

The launch script is like this:
$Source_Folder = '\\filesrv42.sqladmin.lan\DBA\SqlAnchor\SqlAnchor_Hta'
$Destination_Folder = 'C:\SQLAdmin\SqlAnchor'
$SqlAnchor_Filenames = 'SqlAnchor.hta','anchor.ico','SqlAnchor.DetailPages.js'

# Make sure destination folder is available
if (!(Test-Path -path $Destination_Folder)) { New-Item $Destination_Folder -Type Directory }

# Copy HTA files
foreach ($Filename in $SqlAnchor_Filenames) {
  Copy-Item "$Source_Folder\$Filename" -Destination "$Destination_Folder\$Filename" -Force
}

# Start HTA
& "$env:SystemRoot\System32\mshta.exe" "$Destination_Folder\SqlAnchor.hta"

The shortcut for the launch script is
powershell.exe -WindowStyle "Hidden" -File "\\filesrv42.sqladmin.lan\DBA\SqlAnchor\SqlAnchor.Launch.ps1" -ExecutionPolicy "Bypass"

When the shortcut is activated by the user, PowerShell is started and the launch script is loaded. This can be done from a file share as the PowerShell execution policy is bypassed.
Disclaimer: If you use this solution, it is your responsibility to be compliant.

The launch script copies the files for the HTA to a local folder. If the folder does not exist, it is created.
If the files are present in the local folder, they are replaced.

Finally the launch script starts the HTA host „mshta.exe“ and load the hta script.
When the hta script is loaded, the launch script finish with PowerShell.

Back from vacation

What happened while I was away on vacation?
SELECT
  [databases].[name],
  [server_principals].[name] AS [database_owner_name],
  [databases].[create_date],
  [databases].[state_desc]
FROM [master].[sys].[databases]
INNER JOIN [master].[sys].[server_principals] ON
  [databases].[owner_sid] = [server_principals].[sid]
WHERE
  [databases].[database_id] >= 4 AND  -- Filter out system databases
  [databases].[create_date] >= '2012-07-27';

Readable seconds

When I restored a SQL Server database, the message ended with a status of the restore:
RESTORE DATABASE successfully processed 15025885 pages in 3372.932 seconds (34.803 MB/sec).

That is a lot of seconds, but I was asked by management about the restore time. Just giving a couple of thousands of seconds was just not good enough.
But PowerShell came to the rescue with the CmdLet New–TimeSpan:
(New-TimeSpan -Seconds 3372.932).ToString()
that gives
00:56:13

The format is hh:mm:ss given by the ToString() method to the .NET TimeSpan structure.

As the CmdLet is based on the .NET TimeSpan structure, and PowerShell is integrated with .NET, it is also possible to use the static method FromSeconds() exposed by the structure:
[System.TimeSpan]::FromSeconds(3372.932).ToString()

The result is exactly the same — as expected.

ISO 4217 Currency list

To maintain a list of currencies, I have been looking for a standard and a external resource.
The standard ISO 4217 is described at Wikipedia, and though that article I found the resource at iso.org in a XML document.

A quick way to read the currency list using PowerShell:
[xml]$ISO4217 = (New-Object System.Net.WebClient).DownloadString('http://www.currency-iso.org/dl_iso_table_a1.xml')

Write-Verbose -Message "Currency count = $($ISO4217.ISO_CCY_CODES.ISO_CURRENCY.count)" -Verbose

foreach($Currency in $ISO4217.ISO_CCY_CODES.ISO_CURRENCY) {
  $Currency
}

The output on a currency is like this:
ENTITY          : DENMARK
CURRENCY        : Danish Krone
ALPHABETIC_CODE : DKK
NUMERIC_CODE    : 208
MINOR_UNIT      : 2

When you have the currency list, I think it is rather simple to update a internal list, e.g. in a database table.

You could add properties like last update timestamp or private identifier to meet your own needs.

Also the wikipedia article has a list of historical currency codes, that could be added to the internal list.

Logging in T-SQL scripts

I am working on a major version upgrade of a system that is using SharePoint and private databases. In both cases with 100+ GB data. Most database parts are scripted, and some tasks are running for hours.
Still it is important to the planning to know the execution time of the tasks and their steps.

This logging and timing I have done by wrapping the steps in some messages:
DECLARE @start datetime2 = SYSDATETIME();
DECLARE @errmsg nvarchar(2047) = CONVERT(nchar(23), @start, 126) + N'Z : Start';
RAISERROR(@errmsg,0,0) WITH NOWAIT;

-- Do something
WAITFOR DELAY '00:00:04';  -- HH:mm:ss

DECLARE @finish datetime2 = SYSDATETIME();
DECLARE @duration int = DATEDIFF(ms, @start, @finish);
SET @errmsg = CONVERT(nchar(23), @finish, 126) + N'Z : Finish after %i ms.';
RAISERROR(@errmsg, 0,0, @duration) WITH NOWAIT;

The duration of the step is in this case measured in milliseconds (ms), but could be measured in another unit. Unfortunately this unit can not be in a parameter, or as they say in the documentation on DATEDIFF() „User-defined variable equivalents are not valid.“.

I do prefer RAISERROR to PRINT to log in T-SQL. There are many discussions about this, but my take is that with RAISERROR ... WITH NOWAIT I will get the message immidiately and not when the batch block is finished and the PRINT–queue is processed.

PowerShell thoughts

To get a readable duration instead of a lot of ms like 56135000 you can create a TimeSpan object in PowerShell.
PS> \([System.TimeSpan]::FromMilliseconds(56135000)).ToString()
This will give a formatted timespan
15:35:35
Which is 15 hours, 35 minutes and 35 seconds.

If I have a larger script file with several steps, I would go for a PowerShell solution as I then would have better possibilities to log and use general parameters.

History

2012-06-26 Entry created.
2015-02-05 Section about TimeSpan object added.
2023-02-23 Formatting on @duration changed to conversion specification. And timestamp simplyfied.