Home > SQLServer, SQLServer 2005, SQLServer 2008, SQLServer 2008 R2, Undocumented Functions > Accessing Registry using XPs (contd..) – TSQL

Accessing Registry using XPs (contd..) – TSQL

While xp_regread read values from registry under exact path specified. To read instance specific registry entries from registry you can use xp_instance_regenumvalues and xp_instance_regread.

xp_instance_regread translates the given path to instance-specific path in the registry:

for example, executing following code against a SQL 2008 Instance returns the default database location which is specific to that instance:

– © 2011 – Vishal (http://SqlAndMe.com)

 

DECLARE @returnValue NVARCHAR(500)

EXEC   master..xp_instance_regread

       @rootkey      = N'HKEY_LOCAL_MACHINE',

       @key          = N'SOFTWARE\Microsoft\MSSQLServer\Setup',

       @value_name   = N'SQLDataRoot',

       @value        = @returnValue output

PRINT @returnValue

[EDIT: Above path returns the default installation data directory, to get default data directory use path: SOFTWARE\Microsoft\Microsoft SQL Server\MSSQLServer]

this returns:

C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL

 

Now, if I execute the same code against a SQL 2005 Instance on my system, it returns:

 

C:\Program Files\Microsoft SQL Server\MSSQL.2\MSSQL

 

If the key path cannot be translated to instance-specific path, it will read from the specified key path.

 

Hope This Helps!

Vishal

If you like this post, do like my Facebook Page -> SqlAndMe
EMail me your questions -> Vishal@SqlAndMe.com
Follow me on Twitter -> @SqlAndMe

About these ads
  1. David
    January 4, 2012 at 4:33 am

    What if you have multiple instances (and versions) of SQL Server installed…how can you tell where the “default” data path is for each instance/version dynamically…without having to hard code the registry path

  1. July 8, 2011 at 12:24 pm
  2. July 8, 2011 at 12:25 pm
  3. July 29, 2011 at 9:17 pm
  4. August 23, 2011 at 9:17 pm
  5. September 19, 2011 at 11:03 am
  6. October 4, 2011 at 2:49 pm
  7. October 14, 2011 at 2:10 pm
  8. October 17, 2011 at 2:35 pm
  9. January 5, 2012 at 2:48 pm
  10. May 1, 2013 at 10:48 am
  11. May 6, 2013 at 5:16 pm
  12. May 6, 2013 at 5:16 pm
  13. August 20, 2013 at 12:44 pm
  14. August 20, 2013 at 12:46 pm

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 268 other followers

%d bloggers like this: