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 thisfunction 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' `
-VerboseHistory
2010-09-15 First entry.2013-09-09 PowerShell added.
2013-11-19 Registry Value Types handled correct.
Posts
