Archive

Posts Tagged ‘Registry’

Accessing Registry using XPs (contd..) – TSQL

11.05.2011 No comments

We can use the xp_regwrite undocumented extended stored procedure to write new entries to Windows registry.

You need to provide the key path, A new key will be created if it does not exist.

The following code will add a new entry in startup programs list in registry:

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

EXEC master..xp_regwrite
     @rootkey     = 'HKEY_LOCAL_MACHINE',
     @key         = 'SoftwareMicrosoftWindowsCurrentVersionRun',
     @value_name  = 'Solitaire',
     @type        = 'REG_SZ',
     @value       = 'C:Program FilesMicrosoft GamesSolitaireSolitaire.exe'

 

Output returned by the procedure will be '(0) rows affected', unless it encounters an error.

 

To verify the entry, you need to open Registry Editor and traverse to specified path.

 

image

 

Or you can also use xp_regread or xp_instance_regread to verify the entry.

 

You can delete an registry entry using xp_regdelete.

 

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

EXEC master..xp_regdeletevalue
     @rootkey     = 'HKEY_LOCAL_MACHINE',
     @key         = 'SoftwareMicrosoftWindowsCurrentVersionRun',
     @value_name  = 'Solitaire'

 

Output returned by the procedure will be '(0) rows affected', unless it encounters an error.

 

Hope This Helps!

Vishal

Accessing Registry using XPs (contd..) – TSQL

10.05.2011 3 comments

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'SOFTWAREMicrosoftMSSQLServerSetup',

       @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: SOFTWAREMicrosoftMicrosoft SQL ServerMSSQLServer]

this returns:

C:Program FilesMicrosoft SQL ServerMSSQL10_50.MSSQLSERVERMSSQL

 

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

 

C:Program FilesMicrosoft SQL ServerMSSQL.2MSSQL

 

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

 

Hope This Helps!

Vishal

Accessing Registry using XPs – TSQL

09.05.2011 1 comment

You can use the undocumented extended stored procedure xp_regread to access registry entries using T-SQL.

Syntax:

xp_regread    @rootkey      = N'rootkey',
              @key          = N'key',
              @value_name   = N'value_name',
              @value        = @outputValue OUTPUT

 

For example, below code returns "C:Program Files" on my system :

DECLARE @returnValue NVARCHAR(100)
EXEC   master.dbo.xp_regread
       @rootkey      = N'HKEY_LOCAL_MACHINE',
       @key          = N'SOFTWARE\MicrosoftWindows\CurrentVersion',
       @value_name   = N'ProgramFilesDir',
       @value        = @returnValue output
SELECT @returnValue

 

xp_regread ca only be used to retrieve a single value. If you need to retrieve multiple values, you will need to use xp_instance_regenumvalues, which returns all values under a specified key.

 

For example, To retrieve a list of start-up programs from registry we will use:

 

EXEC   master..xp_instance_regenumvalues
       @rootkey = N'HKEY_LOCAL_MACHINE',
       @key     = N'SOFTWARE\Microsoft\Windows\CurrentVersion\Run'

 

this will return all entries under HKLMSoftwareMicrosoftWindowsCurrentVersionRun:

 

image 

 

Hope This Helps!

Vishal