2013-09-10

xp_instance_regwrite syntax

The stored procedure "master.sys.xp_instance_regwrite" is not documented in SQL Server Books Online (BOL), but is usefull. In general the syntax is
EXECUTE [master].[sys].[xp_instance_regwrite]
  @rootkey = N'<name of root key>'
 ,@key = N'<key name>'
 ,@value_name = N'<key name value>'
 ,@type = N'<key type>'
 ,@value = N'<key value>';


For example the number of SQL Server Errorlog files can be updated by the statement
EXECUTE [master].[sys].[xp_instance_regwrite]
  @rootkey = N'HKEY_LOCAL_MACHINE'
 ,@key = N'Software\Microsoft\MSSQLServer\MSSQLServer'
 ,@value_name = N'NumErrorLogs'
 ,@type = N'REG_DWORD'
 ,@value = 42;


Use with caution!

The parameter @type takes a value without N'' around (@type = <key type>), but I like the Unicode framing. This way I try to avoid difficulties when building the values outside the call.

The type of the value for the parameter @value depends on the registry value type.
If the registry value type is a integer like REG_DWORD, the parameter value must be a integer.
 ,@value = 42;
If the registry value type is a string like REG_SZ, the parameter value must be a string.
 ,@value = N'John';

PowerShell usage

If the usage of this (undocumented) procedure should be included in a PowerShell script, it could be with ADO.NET in a function like this
function Write-MsSqlDbInstanceRegistry
{
  [CmdletBinding()]
  Param (
    # help description
    [Parameter(Mandatory=$true, Position=0)]
    [System.Data.SqlClient.SqlConnection]$MsSqlDbCnn,

    # Registry key path
    [Parameter(Mandatory=$true, Position=1)]
    [String]$RegKey,

    # Registry key name
    [Parameter(Mandatory=$true, Position=2)]
    [String]$RegName,

    # Registry key type
    [Parameter(Mandatory=$true, Position=3)]
    [String]$RegType,

    # Registry key value
    [Parameter(Mandatory=$true, Position=4)]
    [String]$RegValue
  )

  Begin {
    "{0:s}Z Write-MsSqlDbInstanceRegistry( '$RegKey', '$RegName', '$RegType', '$RegValue')" -f $([System.DateTime]::UtcNow) | Write-Verbose
  }

  Process {
    $MsSqlDbCmd = New-Object System.Data.SqlClient.SqlCommand
    $MsSqlDbCmd.Connection = $MsSqlDbCnn
    $MsSqlDbCmd.CommandText = '[master].[sys].[xp_instance_regwrite]'
    $MsSqlDbCmd.CommandType = [System.Data.CommandType]::StoredProcedure

    $MsSqlDbCmd.Parameters.Add("@rootkey", [System.Data.SqlDbType]::NVarChar, 128) | Out-Null
    $MsSqlDbCmd.Parameters['@rootkey'].Direction = [System.Data.ParameterDirection]::Input
    $MsSqlDbCmd.Parameters['@rootkey'].Value = 'HKEY_LOCAL_MACHINE'

    $MsSqlDbCmd.Parameters.Add("@key", [System.Data.SqlDbType]::NVarChar, 128) | Out-Null
    $MsSqlDbCmd.Parameters['@key'].Direction = [System.Data.ParameterDirection]::Input
    $MsSqlDbCmd.Parameters['@key'].Value = $RegKey

    $MsSqlDbCmd.Parameters.Add("@value_name", [System.Data.SqlDbType]::NVarChar, 128) | Out-Null
    $MsSqlDbCmd.Parameters['@value_name'].Direction = [System.Data.ParameterDirection]::Input
    $MsSqlDbCmd.Parameters['@value_name'].Value = $RegName

    $MsSqlDbCmd.Parameters.Add("@type", [System.Data.SqlDbType]::NVarChar, 128) | Out-Null
    $MsSqlDbCmd.Parameters['@type'].Direction = [System.Data.ParameterDirection]::Input
    $MsSqlDbCmd.Parameters['@type'].Value = $RegType

    switch -CaseSensitive ($RegType) {
      'REG_DWORD' {
        $MsSqlDbCmd.Parameters.Add("@value", [System.Data.SqlDbType]::Int) | Out-Null
        $MsSqlDbCmd.Parameters['@value'].Value = [Int]$RegValue
      }
      'REG_SZ' {
        $MsSqlDbCmd.Parameters.Add("@value", [System.Data.SqlDbType]::NVarChar, 128) | Out-Null
        $MsSqlDbCmd.Parameters['@value'].Value = $RegValue
      }
      default {
        "{0:s}Z Unknown Registry Value Type '$RegKeyType'." -f $([System.DateTime]::UtcNow) | Write-Error
        return
      }
    }
    $MsSqlDbCmd.Parameters['@value'].Direction = [System.Data.ParameterDirection]::Input

    $MsSqlDbCnn.Open()
    [Int]$RowCount = $MsSqlDbCmd.ExecuteNonQuery()
    $MsSqlDbCnn.Close()
  }

  End {}
}

The PowerShell function can be used like this
$Cnn = New-Object System.Data.SqlClient.SqlConnection
$Cnn.ConnectionString = 'Data Source=(local);Integrated Security=SSPI;Application Name=MsSqlDbInstanceRegistry'

Write-MsSqlDbInstanceRegistry `
  -MsSqlDbCnn $Cnn `
  -RegKey 'Software\Microsoft\MSSQLServer\MSSQLServer' `
  -RegName 'NumErrorLogs' `
  -RegType 'REG_DWORD' `
  -RegValue '42' `
  -Verbose


History

2010-09-15  First entry.
2013-09-09  PowerShell added.
2013-11-19  Registry Value Types handled correct.

1 comment:

Raoul said...

Yes, it also very useful to change authentication mode in SQL Server by using SQL code.

- Raoul

Web: http://guide-line.com/
Blog: http://guide-line.com/archives/category/raouls-corner