Read any Windows Registry string value from T-SQL

Last week 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. In the 1990's software distribution issues were dominated by DLL Hell and the Windows registry.

Not that the Windows registry has gone away. It's still very much with us and will be at least until the Longhorn era and I suspect even after that. We've got live with it.

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:

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE  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 http://www.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 
http://www.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's the results from a sample query that reads the path to SQL Server tools.

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]
go

(Results)
Path to the Tools directory
----------------------------------------------------------------
C:\Program Files\Microsoft SQL Server\80\Tools

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 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.

Do you have a great UDF that you'd like to share?  Or maybe you have a T-SQL problem that you think could be solved by a UDF but you don't know how? Send them to: UDF@NovickSoftware.com

and they might be published in this newsletter.  I try and respond to every request that I get. 

Thanks, 
Andrew Novick