SQL Server – Reading Instance Configuration saved in Registry

Background

For a script that I am working on, I need to access System Properties such as the base folder of the errorlog.

 

SQL Server Management Studio ( SSMS )

Of course the information is available via SSMS, just click on the Server’s Property and you will see something like this…

sqlserver-mssqlserver-20170119-1107am

 

Explanation

  1. We can that “Root Directory” is “C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL

 

Registry

Registry – CPE

Knowing the value that we are searching for took to the registry and searched for “C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL“.

Ended up here…

sqlserver-cpe

Registry – Setup

Most of the most important information is saved in the Setup branch.

sqlserver-setup-20170119-1126am

 

Registry – MSSQLServer

Btw, the MSSQLServer path is very important, as well.

sqlserver-mssqlserver-20170119-1116am

Code

xp_instance_regread

Let us read ErrorDumpDir from the CPE branch

 

Code Snippet


DECLARE @rootKey	 NVARCHAR(500)
DECLARE @path		 NVARCHAR(500)
DECLARE @name		 NVARCHAR(500)
DECLARE @value		 NVARCHAR(1000)

set @rootKey = N'HKEY_LOCAL_MACHINE'
set @path    = N'SOFTWARE\Microsoft\MSSQLServer\CPE'
set @name    = N'ErrorDumpDir'

EXEC master..xp_instance_regread
         @rootkey = @rootKey
       , @path    = @path
       , @name	  = @name
       , @value   = @value output

PRINT 'Error Dump Dir :' + isNull(@value, '')


 

Output

errordump

 

Knowledge Base

Please keep in mind that xp_instance_regread is an extended Stored Procedure.

As there could be a few SQL Server Instances on a box, Microsoft abstracted things a bit and xp_instance_regread is able to read information for the specific Instance it is ran on.

The more generic SP that does not abstract for the specific instance is xp_RegRead.