Sponsored By Aspose - File Format APIs for .NET

Aspose are the market leader of .NET APIs for file business formats – natively work with DOCX, XLSX, PPT, PDF, MSG, MPP, images formats and many more!

Manipulating the registry using T-SQL

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.

This entry was posted in Sql Development. Bookmark the permalink. Follow any comments here with the RSS feed for this post.

6 Responses to Manipulating the registry using T-SQL

  1. Vishnu saxena says:

    Hi Guy’s

    i am trying to write a small TSQL code and i am a very bigener in TSQL… can you guys help me on this…
    i need to read a specific registry entry from HKEY_CURRENT_CONFIG using TSQL, but when i am using master.dbo.xp_instance_regread, it throws an error saying “Error executing Read extended stored procedure: Invalid Predefined Registry system key” i checked the registry entry and its present there under HKEY_CURRENT_CONFIG\SOFTWARE\Maestro\IMPORT/EXPORT\ENTRIES\TNT\DBConfig

  2. Randy says:

    Paul, the DefaultData key-value does not exist just after the install. It is created when the default is explicitly set. Using the SQLPath plus DATA is the default to use when the other key does not exist or is null.

  3. Paul Houle says:

    @Jeffery,

    I found this page quite useful because I’m writing a C# program that automatically installs a database in a particular state. Part of that process is restoring a backup file: by default, SQL Server will try to create the .mdb file in the same places the .mdb file was when the backup was made. Well, that’s not acceptable if:

    (i) You’re installing on a machine that installed SQL Server in a different place, or
    (ii) You want to have more than one copy of the database running.

    We need to do both. It’s easy to use the “MOVE” clause of restore to tell SQL Server where to put the .mdb file, but you need to know what directory it goes in… The registry keys above are a big help for that.

    It wouldn’t be crazy to build a stored procedure that does something like this, in which case using the above XP in your stored procedure would be just the ticket.

    Here’s the trouble I’ve got. In SQL Server 2008, I can find the “SQLPath” but I can’t find the “DefaultData” key. In the short term I can just tack “\Data\” onto the SQLPath, but I’d really rather get the value of “DefaultData”… I poked around with RegEdit and couldn’t find anything… Any suggestions?

  4. No one big problem that I haven’t been able to solve, is that I need to test if a registry key exists. The problem with these undocumented procs is that they don’t through error code. I can check if a key exists or not by running the xp_regread in the UI, but I cannot do it from my stored proc because it will through an error message from the platform API. It will be great if they can wrap the error message from the platform API to the T-SQL error code, but anyway, this is unsupported procs…

  5. LOL JP. I don’t use them myself (have on rare occasion wrote to reg though when installing mutliple instances of sql server programmtically), but there are people who do. Most people use them to do nothing more than read particular path information related to sql server, like log file directories, installation path, data directory etc. They even are shipped with 2005, so people are depending on them still.

    Sorry I didn’t elaborate more on them (taking your comprehensive information remark as sarcasm 😀 ). I just wanted people to be aware they existed, and not necessarily detail out their use as to give the impression I’m an advocate of people using them.

  6. This must be a specialized program of some sort for it to _have_ to read a registry value from T-SQL.

    Thanks for this comprehensive information about these extended stored procedures. I hope I never need them!

Leave a Reply