A while ago I did a presentation about software distribution for Smart Client applications using .Net technologies. Part of the discussion was about the history of software distribution. Fifteen years ago problems were often dominated by DLL Hell and the Windows registry. DLL handling has improved over the years. Windows registry hasn’t gone away. It’s still with us even into Windows 10.
This issue’s UDF, udf_Sys_RegReadStr, helps you live with the Windows registry. It’s a thin wrapper around the undocumented extended stored procedure xp_regread that can be used to read a string value from the registry. Here’s the CREATE FUNCTION script:
GO
SET ANSI_NULLS ON
GOCREATE FUNCTION dbo.udf_SYS_RegReadStr (
@Hive as nvarchar(128) — Hive to be read
, @Key as nvarchar(255) — Path to the registry entry
, @Item as nvarchar(128) — Name of the item
) RETURNS nvarchar(4000) — The registry string
/*
* Reads a entry out of the registry based on the undocumented extended
* stored procedure xp_regread.
*
* Example:
select dbo.udf_SYS_RegReadStr (N’HKEY_LOCAL_MACHINE’
, N’SOFTWARE\Microsoft\Microsoft SQL Server\80\Tools\ClientSetup’
, N’SQLPath’) as [Path to the Tools directory]
*
* © Copyright 2004 Andrew Novick https://novicksoftware.com
* You may use this function in any of your SQL Server databases
* including databases that you sell, so long as they contain
* other unrelated database objects. You may not publish this
* UDF either in print or electronically.
* Published as T-SQL UDF of Week Newsletter Vol 2 #41 10/19/04
https://novicksoftware.com/UDFofWeek/UDFofWeek.htm
***********************************************************************/
AS BEGIN
DECLARE @sRegistryValue nvarchar(4000)
, @RC int
EXEC @RC = master.dbo.xp_regread @Hive
, @Key
, @Item
, @sRegistryValue OUT
, N’no output’
RETURN @sRegistryValue
END
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
GRANT EXEC ON dbo.udf_SYS_RegReadStr to PUBLIC
GO
To use this function you must supply the hive, key, and item names. Note that udf_Sys_RegReadStr only works for string values.
Here are the results from a sample query that reads the path to SQL Server tools.
, N’SOFTWARE\Microsoft\Microsoft SQL Server 2019 Redist\TSqlLanguageService\1033\CurrentVersion’
, N’Version’) as [SQL Version string]
go
Results:
| SQL Version string |
|---|
| 15.0.2000.5 |
I use one of SQL Server’s entries because it makes an easy example but you may read any registry entry. That is, so long as the service account that SQL Server is running as has permission to read the entry. And remember, the registry read is performed on the machine that SQL Server is running on. Not on the client machine.
What’s equally remarkable is that I wrote this function for SQL 2000 and it continues to work in SQL 2019. Of course, that’s for the Windows versions. The Linux versions don’t have a registry and don’t have extended stored procedures. That’s one of the few features that don’t work on Linux, which is a good thing.