Part 1  |  Part 2

The Windows Registry is a hierarchical structure for storing valuable information about the hardware and software installed on a computer.

Why would anyone what to read or write to the Windows Registry from a SQL Server Stored Procedures or T-SQL Script? The information stored in the Registry can be useful information for determining how your SQL Server environment is configured. Or, someone with a custom-built application might want to create some registry entries to store application setup information for their custom application. The configuration information stored in the Windows Registry is available to Stored Procedures and T-SQL scripts by using undocumented Extended Store Procedures.

This article will explore how to read and update Windows registry entries. This is the first part in a multi-part series discussion about undocumented Extended Stored Procedures for working with the Windows Registry.

Understanding the Windows Registry

The Windows Registry is a hierarchical storage structure or database for storing information about users, hardware, configuration of software and, property setting. In the scope of this article, I will not be going into great detail on the architecture of the registry. If you want more detailed information about the Windows registry, you can use the Windows “Help” function. In addition to that, here is a site that you that should provide you with enough information to better understand the Windows Registry:

http://www.windowsitlibrary.com/Content/224/1.html

Note that only users with rights to access the Windows Registry and the master database will be able to use the executables and Extended Stored Procedures in this article. Since the registry contains lots of information and some of this information has some security implications, you would not want to allow just anyone to read this information.

One of the easiest ways to view and edit the registry from Windows is using the “regedt32,” an executable provided with Windows 2000. Here is a display of the high-level Windows Registry structures on my machine, displayed by using “regedt32.”

As you can see, there are five different high-level registry structures. By clicking on the “+” sign next to each entry you can drill down in any one of the structures to determine what is stored in the registry sub-tree. The SQL Server registry information can be found throughout the registry. But the bulk of information about the default SQL Server installation can be found under the “HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer” registry structure.

Interesting Registry Keys for SQL Server

Click here for a list in which I have identified a number of SQL Server Registry Keys that might be of some interest. These are some of the common ones that I have used in different Stored Procedures and T-SQL scripts when using the Extended Stored Procedures to read and write to the registry. This is by no means a complete list.

How to Read the Registry Using xp_regread

The undocumented Extended Stored Procedure named “xp_regread,” which comes with SQL Server, can be used to read any registry entry. The following is the syntax used to call this Extended Stored Procedure:

EXECUTE master..xp_regread
               'HKEY',
               ’KEY VALUE',
               'STING VALUE',
               @OUTPUTVAR OUTPUT

Where:

• “HKEY’” is one of the following high level sub-trees:
• HKEY_LOCAL_MACHINE
• HKEY_CURRENT_USER
• HKEY_CURRENT_CONFIG
• HKEY_USERS
• HKEY_CLASSES_ROOT ‘KEY VALUE’ is the registry key value you are interested in reading
• ‘“STRING VALUE’” is the registry string value you want to read
• @OUTPUTVAR is a locally defined variable the will be set to the value of the registry
     key being read

Let me demonstrate how “xp_regread” can be used to return a registry entry to a calling program. Here is some T-SQL code to return the default directory for storing database backups:

DECLARE @BACKUP_DIR VARCHAR(200)
   EXECUTE master..xp_regread
        'HKEY_LOCAL_MACHINE',
        'SOFTWARE\Microsoft\MSSQLServer\MSSQLServer',
        'BackupDirectory',
        @BACKUP_DIR OUTPUT
   PRINT @BACKUP_DIR

When I run this script on my machine the following output is produced:

        C:\Program Files\Microsoft SQL Server\MSSQL\BACKUP

Being able to read the registry from a T-SQL script allows me to build processes that use these registry values to drive the outcome of each process. For an example, I built a custom backup process that reads the registry to determine where to find database backups, so I can copy them to an alternate location for disaster recovery purposes. Here is an example of a script that does just that. This script copies all backups that have “master” in the name to an alternate location:

   DECLARE @BACKUP_DIR VARCHAR(200)
   DECLARE @ALTERNATIVE_DIR VARCHAR(200)
   DECLARE @CMD VARCHAR(1000)
   SET @ALTERNATIVE_DIR = '\\SERVER2\SERVER1\BACKUP\'
   EXECUTE master..xp_regread
                      'HKEY_LOCAL_MACHINE',
                      'SOFTWARE\Microsoft\MSSQLServer\MSSQLServer',
                      ' BackupDirectory ',
                      @BACKUP_DIR OUTPUT
   SET @CMD = 'COPY "' + @BACKUP_DIR + '\*MASTER*" ' + 
                   @ALTERNATIVE_DIR
   EXEC master..xp_cmdshell @CMD

How to Write to the Registry Using xp_regwrite

Depending on your application requirements, you might want to write to the registry. You may want to do this in order to change the setting of one of the registry entry. SQL Server provides the undocumented Extended Stored Procedure “xp_regwrite” to accomplish this. Here is the syntax for using this Extended Stored Procedure:

EXECUTE master..xp_regwrite   
      'HKEY',
      ’KEY VALUE',
      'STING VALUE',
      ‘Data Type’,
      ‘Value to Write’
 
Where:   

• “HKEY” is one of the following high level sub-trees:
  • HKEY_LOCAL_MACHINE
  • HKEY_CURRENT_USER
  • HKEY_CURRENT_CONFIG
  • HKEY_USERS
  • HKEY_CLASSES_ROOT
• “KEY VALUE”is the registry key value to be written
•  “STRING VALUE” is the registry string value to be written
• “Data Type” is one of the following:
• REG_SZ — This type is used for text string
• REG_EXPAND_SZ — This type is used for text strings, but the text string can contain an environment variable that when read by a program can be expanded. Note the program will need to handle expanding the environment variable. 
• REG_MULTI_SZ — This type allows for storing an array of delimited text strings in the registry
• REG_DWORD — This type is use to store a 4-byte numeric value
• REG_BINARY — This data type is used when you want to store raw binary data.

  Note: There are other data types, but these are the common ones 

• “Value to Write” — The value to be written to the registry

Here is an example that uses “xp_regwrite” to update the registry entry: “HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\MSSQLServer\BackupDirectory”:

    DECLARE @BACKUP_DIR VARCHAR(200)
    SET @BACKUP_DIR = 'c:\backup'
    EXECUTE master..xp_regwrite 
                'HKEY_LOCAL_MACHINE', 
                'SOFTWARE\Microsoft\MSSQLServer\MSSQLServer',
                'BackupDirectory',
                'REG_SZ',
                @BACKUP_DIR

In this example, I changed my default backup directory to be “c:\backup.”

Using “xp_regwrite” has the potential for corrupting the registry. Care needs to be taken when you are using “xp_regwrite” to avoid corrupting the registry. So make sure you know the consequences of updating a registry entry prior to running “xp_regwrite.”

One of the things you can do with “xp_regwrite” is to create your own Windows Registry entries. This may be useful if you are writing software and want to store configuration information and/or properties for your software in the registry. Here is an example where I created a new registry entry to store some vital statistics information about me:

                DECLARE @VALUE VARCHAR(200)
                SET @VALUE = 'Gregory'
                EXECUTE master..xp_regwrite
                                'HKEY_LOCAL_MACHINE',
                                'SOFTWARE\Larsen\Info\',
                                'FirstName',
                                'REG_SZ',
                                @VALUE 
                SET @VALUE = 'Larsen'
                EXECUTE master..xp_regwrite
                                'HKEY_LOCAL_MACHINE',
                                'SOFTWARE\Larsen\Info\',
                                'LastName',
                                'REG_SZ',
                                @VALUE

Here I have created a registry structure named “HKEY_LOCAL_MACHINE\SOFTWARE\Larsen\Info\” which contains to registry entries: “FirstName” and “LastName.” These entries are used to store my first and last name. So you can see it is easy to create new registry entries using “xp_regwrite.”

Considerations When Using Undocumented Features

Microsoft has not documented either of these Extended Stored Procedures (“xp_regread”, “xp_regwrite”). Due to this fact, Microsoft might decide to change the functionality of these Extended Stored Procedures during some future SQL Server upgrade. Therefore, you need to use some level of caution prior to upgrading SQL Server if you decide to use these Extended Stored Procedures in critical parts of an application. I suggest that you thoroughly test any application that uses these Extended Stored Procedures against any new release or patch of SQL Server to verify that they still function as expected.

Conclusion

Getting and storing information in the Windows Registry, using the undocumented Extended Stored Procedures available in SQL Server, is very easy. Being able to read and update the Registry allows applications to make decisions based on the information in the registry. I’ve found that being able to read and write Registry information is extremely useful in building custom T-SQL code to manage my SQL Server environment. Hopefully next time you what to know or save some Windows Registry information you will be able to use these undocumented Extended Stored Procedures. In my next article, in this series, I will discuss additional undocumented Extended Stored Procedures for manipulating information stored in the Windows Registry.

--

Gregory A. Larsen is a DBA at Washington State Department of Health. He has been working with computers since the late 1970s, and has a BS in Computer Science, with a minor area of study in Mathematics. During Greg’s DBA career, he has managed databases on Windows, Unix, and IBM mainframes; before becoming a DBA, he developed and maintained applications. Greg has written a number of articles for different Web publishing sites, as well as SQL Server Magazine. Greg also maintains a Website that contains a number of SQL Server examples to common DBA and SQL developer issues.