This post originated from an RSS feed registered with .NET Buzz by Raymond Lewallen. Original Post: Manipulating the registry using T-SQL Feed Title: Raymond Lewallen Feed URL: /error.htm?aspxerrorpath=/blogs/raymond.lewallen/rss.aspx Feed Description: Patterns and Practices, OOP, .Net and Sql Latest .NET Buzz Posts Latest .NET Buzz Posts by Raymond Lewallen Latest Posts From Raymond Lewallen

Well, after a 3 week vacation from blogging, I’m back.  The past month has been very busy for me, but its time I get back on track.

So I had somebody send me some code that they were having problems with.  They were trying to use T-SQL to read a value from the local registry.  When I saw that the code was about 70 lines long, I didn’t even really examine it, as there is a much easier way to get values from the registry.

In the master database, there is an extended stored procedure called “xp_instance_regread” that allows you to read values from the registry.  The reason most people don’t know about it is because you won’t find any information in BOL.  If its undocumented, this means that they are unsupported by Microsoft and you have to google to figure out how to use them, or ask somebody who knows.

Here is a quick example on using xp_instance_regread to get the installation path of Sql Server, and another one that tells you where the default data directory is for Sql Server.

declare @returnvalue int,

@path nvarchar(4000)

exec @returnvalue = master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE',N'Software\Microsoft\MSSQLServer\Setup',N'SQLPath', @path output, 'no_output'

print @path

go

declare @returncode int,

@path nvarchar(4000)

exec @returncode = master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE',N'Software\Microsoft\MSSQLServer\MSSQLServer',N'DefaultData', @path output, 'no_output'

print @path

In addition to an extended stored procedure to read values from the registry, there are other xp’s that allow you to manipulate the registry as well.

• xp_instance_regaddmultistring
• xp_instance_regdeletekey
• xp_instance_regdeletevalue
• xp_instance_regenumkeys
• xp_instance_regenumvalues
• xp_instance_regremovemultistring
• xp_instance_regwrite

As always, use these extended stored procedures with EXTREME CAUTION!  Also, anybody can use xp_instance_regread, but you have to be in the sysadmin role to change, write and delete registry values and keys.