Thursday, April 5, 2012

Reading & Writing to the Registry from SQL Server

Fetching data from the registry - 

DECLARE @path NVARCHAR(4000)

EXECUTE [master].[dbo].[xp_instance_regread]
 , N'HKEY_LOCAL_MACHINE'
 , N'Software\Microsoft\MSSQLServer\MSSQLServer',N'BackupDirectory'
 , @path OUTPUT 
 , 'no_output'
SELECT @path AS DefaultBackupDirectory

Result - C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Backup 

Writing data to the registry -

DECLARE @path NVARCHAR(4000)
SET @path ='C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Backup'

EXECUTE [master].[sys].[xp_instance_regwrite]
    N'HKEY_LOCAL_MACHINE'
  , N'Software\Microsoft\MSSQLServer\MSSQLServer'
  , N'BackupDirectory'
  , N'REG_SZ'
  , @path;

2nd example, using named parameters -

DECLARE @path NVARCHAR(4000)
SET @path ='C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Backup' 
 
EXECUTE [master].[sys].[xp_instance_regwrite]
  @rootkey = N'HKEY_LOCAL_MACHINE'
 ,@key = N'Software\Microsoft\MSSQLServer\MSSQLServer'
 ,@value_name = N'BackupDirectory'
 ,@type = N'REG_SZ'
 ,@value = @path;