使用dbatools的Install-DbaInstance时参数绑定错误排查

问题描述 #

使用PowerShell参数拼接方式调用Install-DbaInstance时，出现类型转换或参数验证错误，直接内联参数则运行正常。

报错信息1： #

Install-DbaInstance : Cannot process argument transformation on parameter 'SqlInstance'. Cannot convert value "System.Collections.Hashtable" to type
"Sqlcollaborative.Dbatools.Parameter.DbaInstanceParameter[]". 
Error: "Cannot convert value "System.Collections.Hashtable" to type "Sqlcollaborative.Dbatools.Parameter.DbaInstanceParameter". Error: "Failed to interpret input as Instance: System.Collections.Hashtable""

报错信息2（当Version为哈希表第一个键时）： #

Install-DbaInstance : Cannot validate argument on parameter 'Version'. The argument "System.Collections.Hashtable" does not belong to the set "2008,2008R2,2012,2014,2016,2017,2019" specified by the ValidateSet attribute. Supply an argument that is in the set and then try the command again.

原代码： #

#set parameters
$Computer = "server01"
$version = 2019
 
#get service account credentials
$sacredential = get-credential -Message "sa account"
$EngineCredential = get-credential -Message "Engine"
$AgentCredential = get-credential -Message "Agent"
 
$config = @{
ComputerName = $Computer
Version = $version
SqlCollation = "Latin1_General_CI_AS"
AuthenticationMode = "Mixed"
Feature = "Engine"
InstancePath = "D:\Program Files\Microsoft SQL Server"
SaCredential = $sacredential
EngineCredential = $EngineCredential
AgentCredential = $AgentCredential
DataPath = "E:\Data"
LogPath = "F:\Log"
TempPath = "G:\TempDB"
BackupPath = "E:\Backup"
PerformVolumeMaintenanceTasks = $true
}

$result = Install-DbaInstance $config
$result | Format-Table
if (-not $result.Successful) {
    throw "Failed to install SQL Server"
}

问题原因 #

参数拼接的语法使用错误：

• 直接传递$config时，PowerShell会把整个哈希表当作单个位置参数传入命令，而非展开成多个键值对参数。
• 若哈希表第一个键是Version，哈希表会被当作Version参数的值传入，触发验证错误；否则会被当作第一个位置参数SqlInstance传入，导致类型转换失败。

解决方法 #

使用PowerShell参数拼接的正确语法：用@前缀调用哈希表，让PowerShell自动将哈希表的键值对映射到命令的对应参数上。

修正后的代码： #

#set parameters
$Computer = "server01"
$version = 2019
 
#get service account credentials
$sacredential = get-credential -Message "sa account"
$EngineCredential = get-credential -Message "Engine"
$AgentCredential = get-credential -Message "Agent"
 
$config = @{
ComputerName = $Computer
Version = $version
SqlCollation = "Latin1_General_CI_AS"
AuthenticationMode = "Mixed"
Feature = "Engine"
InstancePath = "D:\Program Files\Microsoft SQL Server"
SaCredential = $sacredential
EngineCredential = $EngineCredential
AgentCredential = $AgentCredential
DataPath = "E:\Data"
LogPath = "F:\Log"
TempPath = "G:\TempDB"
BackupPath = "E:\Backup"
PerformVolumeMaintenanceTasks = $true
}

# 使用@config进行参数拼接，代替$config
$result = Install-DbaInstance @config
$result | Format-Table
if (-not $result.Successful) {
    throw "Failed to install SQL Server"
}

关键说明 #

在PowerShell中，参数拼接的核心规则：

• 用哈希表存储参数时，必须通过@哈希表名的方式调用，PowerShell会将哈希表中的每个键识别为参数名，对应的值作为参数值传递。
• 如果使用$哈希表名，则整个哈希表会被当作单个参数传递，完全不符合参数拼接的预期。

内容的提问来源于stack exchange，提问作者Adrian S