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.

Stopwatch

Some time ago I write about timestamp difference, and I have used it for measuring execution times.
But actually there is a much better way.

By using the Stopwatch class (System.Diagnostics.Stopwatch) I get a measument by a single object in stead of two DateTime (System.DateTime) objects.
Also I can take a look at the stopwatch while it is running by the Elapsed property.
The result given is a TimeSpan (System.TimeSpan) object, and by reading the value with the ToString method it is very usefull.

The Stopwatch is created by calling the static method StartNew():
PS C:\> $mywatch = [System.Diagnostics.Stopwatch]::StartNew()
Stopping the stopwatch is
PS C:\> $mywatch.Stop()
Reading the stopwatch without formatting the answer is also quite simple:
PS C:\> $mywatch.Elapsed

Days              : 0
Hours             : 0
Minutes           : 0
Seconds           : 14
Milliseconds      : 975
Ticks             : 149755134
TotalDays         : 0,000173327701388889
TotalHours        : 0,00415986483333333
TotalMinutes      : 0,24959189
TotalSeconds      : 14,9755134
TotalMilliseconds : 14975,5134
Reading the stopwatch with standard formatting [HH:mm:ss.ddddddd] gives you the result more readable:
PS C:\> $mywatch.Elapsed.ToString()
00:15:02.9033008

I use it for execution times. On scripts for the entire script and some subtasks.

Describe database role

When you script out a database role in SQL Server Management Studio (SSMS) or another tool like Red-Gate SQL Compare, you most likely will get just the creation but the rights.
Yesterday I needed the rights for a database role on tables and other database objects. This I got by a small T-SQL script:

:Setvar _RoleName "sqlanchor_user"

DECLARE @script nvarchar(MAX) = N'USE [' + DB_NAME() + N'];' + CHAR(13) + N'GO' + CHAR(13);

SELECT @script += N'CREATE ROLE [' + [db_role].[name] + N'] AUTHORIZATION [' + [owner].[name] + N'];' + CHAR(13)
FROM [sys].[database_principals] AS [db_role]
INNER JOIN [sys].[database_principals] AS [owner]
  ON [db_role].[owning_principal_id] = [owner].[principal_id]
WHERE [db_role].[type] = N'R' AND [db_role].[name] = N'$(_RoleName)';

-- Database object rights
SELECT @script += 'GRANT ' + [db_perm].[permission_name] + ' ON [' + USER_NAME(OBJECTPROPERTY([db_perm].[major_id], 'OwnerId')) + '].[' + OBJECT_NAME([db_perm].[major_id]) + '] TO [' + [db_role].[name] + N'];' + CHAR(13) COLLATE Latin1_General_CI_AS
FROM [sys].[database_permissions] AS [db_perm]
INNER JOIN [sys].[database_principals] AS [db_role]
  ON [db_perm].[grantee_principal_id] = [db_role].[principal_id]
WHERE db_perm.[class] = 1  -- class 1 : Db Object or Column
  AND [db_role].[name] = N'$(_RoleName)';

PRINT @script;

The script only show how to create the database role and assign rights on database objects.
Rights on schemas for example is not by this script. Consider the script as a spike solution, that can be extended.
Take a look in the documentation for "sys.database_permissions" on the possibilities.

Get Backup Directory with ADO.NET

About 1½ year ago I made a entry on this blog on how to get the SQL Server Backup Directory by using the undocumented stored procedure "[master].[sys].[xp_instance_regread]".
Today I needed the path name in a PowerShell script, and I also wanted to call the procedure correct.
This I have done by calling the procedure through ADO.NET as a stored procedure, not in a EXECUTE statement as dynamic SQL.
$ServerName = '(local)'
$cnnStr = "Data Source=$ServerName;Integrated Security=SSPI;Application Name=SqlBackupFolder"
$Cnn = New-Object System.Data.SqlClient.SqlConnection $cnnStr
$Cmd = New-Object System.Data.SqlClient.SqlCommand
$Cmd.Connection = $Cnn
$Cmd.CommandText = '[master].[sys].[xp_instance_regread]'
$Cmd.CommandType = [System.Data.CommandType]::StoredProcedure
$Cmd.Parameters.Add("@rootkey", [System.Data.SqlDbType]::NVarChar, 128) | Out-Null
$Cmd.Parameters['@rootkey'].Direction = [System.Data.ParameterDirection]::Input
$Cmd.Parameters['@rootkey'].Value = 'HKEY_LOCAL_MACHINE'
$Cmd.Parameters.Add("@key", [System.Data.SqlDbType]::NVarChar, 128) | Out-Null
$Cmd.Parameters['@key'].Direction = [System.Data.ParameterDirection]::Input
$Cmd.Parameters['@key'].Value = 'SOFTWARE\Microsoft\MSSQLSERVER\MSSQLSERVER'
$Cmd.Parameters.Add("@value_name", [System.Data.SqlDbType]::NVarChar, 128) | Out-Null
$Cmd.Parameters['@value_name'].Direction = [System.Data.ParameterDirection]::Input
$Cmd.Parameters['@value_name'].Value = 'BackupDirectory'
$Cmd.Parameters.Add("@value", [System.Data.SqlDbType]::NVarChar, 128) | Out-Null
$Cmd.Parameters['@value'].Direction = [System.Data.ParameterDirection]::Output
$Cnn.Open()
$_RowCount = $Cmd.ExecuteNonQuery()
$Cnn.Close()
$SqlBackupFolder = $Cmd.Parameters['@value'].Value
":: SQL Server Backup Folder = '$SqlBackupFolder'."
The answer from the script is like
:: SQL Server Backup Folder = 'C:\MSSQL\Backup'.

It is possible to reduce the number of lines in the script, but this way I can use the call of the procedure for other information than the Backup Directory.

If you plan to reuse the Command object, you should consider to remove the Parameters ($Cmd.Parameters.Clear()) and reset the CommandType ($Cmd.CommandType = [System.Data.CommandType]::Text).