多环境执行PowerShell脚本异常：找不到ServerConnection类型

PowerShell脚本在Visual Studio Code的PowerShell终端中运行完全正常，但通过C#代码调用该脚本，或在Windows资源管理器中选择“以PowerShell运行”时，出现如下错误：

New-Object : Cannot find type [Microsoft.SqlServer.Management.Common.ServerConnection]: verify that the assembly containing this type is loaded.
At line:32 char:18
+         return @(& $origNewObject @psBoundParameters)
+                  ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : InvalidType: (:) [New-Object], PSArgumentException
    + FullyQualifiedErrorId : TypeNotFound,Microsoft.PowerShell.Commands.NewObjectCommand

脚本代码如下：

try {
    Add-Type -AssemblyName "Microsoft.SqlServer.Smo, Version=15.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91"
    Start-Sleep -Seconds 5
}
catch {
    Write-Output "Failed to load the SMO assembly. Error: $($_.Exception.Message)"
    return
}

$SMOServerConn = new-object ("Microsoft.SqlServer.Management.Common.ServerConnection")
$SMOServerConn.ServerInstance="xxx.xxx.xxx.xxx"
$SMOServerConn.LoginSecure=$false
$SMOServerConn.Login='xxxxxx'
$SMOServerConn.Password='xxxxxxxxxxxxxxxxxxxxxxxxxxxx'

$SMOserver = new-object ("Microsoft.SqlServer.Management.Smo.Scripter") #-argumentlist $server
$srv = new-Object Microsoft.SqlServer.Management.Smo.Server($SMOServerConn)
$srv.ConnectionContext.ApplicationName="MySQLAuthenticationPowerShell"  

$db = New-Object Microsoft.SqlServer.Management.Smo.Database
$db = $srv.Databases.Item('xxxxx')

$maxAttempts = 10
$attempt = 0

# Loop until the connection is established or the maximum number of attempts is reached
while ($db.ConnectionContext.IsOpen -eq $false -and $attempt -lt $maxAttempts) {
    $attempt++
    Write-Output "Attempt $($attempt): Connecting to database..."

    # Wait for a short duration before attempting the next connection check
    Start-Sleep -Seconds 1
}


$scripter = new-object ("$SMOserver") $srv

$line="[cccauth].[spGetTrxReconSummary]"
$line = $line.replace('[','').replace(']','')
$splitline =$line.Split(".")

$Objects = $db.storedprocedures[$splitline[1], $splitline[0]]

Write-Host "Extracting stored procedure $($line)"
Write-Host "--------------------------------"
        
$sp_extracted+=$Scripter.Script($Objects)
$sp_extracted=$sp_extracted.replace("SET QUOTED_IDENTIFIER ON", "SET QUOTED_IDENTIFIER ON `r`nGO`r`n")

Write-Host $sp_extracted -ForegroundColor Green

Write-Host "Scripting DB Finished... Press any key..."
[void][System.Console]::ReadKey($true)

补充：若在脚本中添加连接第二个服务器/数据库的逻辑，该连接可正常工作，请问问题出在哪里？

核心原因 #

1. 依赖程序集未完整加载：你只加载了Microsoft.SqlServer.Smo，但Microsoft.SqlServer.Management.Common.ServerConnection类型实际属于Microsoft.SqlServer.ConnectionInfo程序集。VS Code的PowerShell终端可能因为环境预加载了SMO的全套依赖，而直接运行或C#调用的PowerShell环境不会自动加载这些关联程序集。
2. 脚本存在语法错误：$scripter = new-object ("$SMOserver") $srv这行代码逻辑错误——$SMOserver是已经实例化的Scripter对象，不能用它作为类型名来创建新实例。
3. 冗余的等待逻辑：Start-Sleep -Seconds 5完全没必要，Add-Type是同步加载程序集的，加载完成后才会执行后续代码。

修正方案 #

1. 显式加载所有必需的SMO依赖程序集，包括Microsoft.SqlServer.ConnectionInfo和Microsoft.SqlServer.Smo。
2. 修正Scripter实例化的错误代码。
3. 移除无用的Start-Sleep语句。

修正后的脚本示例 #

try {
    # 显式加载所有必需的SMO依赖程序集
    Add-Type -AssemblyName "Microsoft.SqlServer.ConnectionInfo, Version=15.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91"
    Add-Type -AssemblyName "Microsoft.SqlServer.Smo, Version=15.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91"
}
catch {
    Write-Output "Failed to load SMO assemblies. Error: $($_.Exception.Message)"
    return
}

$SMOServerConn = New-Object Microsoft.SqlServer.Management.Common.ServerConnection
$SMOServerConn.ServerInstance="xxx.xxx.xxx.xxx"
$SMOServerConn.LoginSecure=$false
$SMOServerConn.Login='xxxxxx'
$SMOServerConn.Password='xxxxxxxxxxxxxxxxxxxxxxxxxxxx'

$srv = New-Object Microsoft.SqlServer.Management.Smo.Server($SMOServerConn)
$srv.ConnectionContext.ApplicationName="MySQLAuthenticationPowerShell"  

$db = $srv.Databases.Item('xxxxx')

$maxAttempts = 10
$attempt = 0

# 循环检查连接状态
while (-not $db.ConnectionContext.IsOpen -and $attempt -lt $maxAttempts) {
    $attempt++
    Write-Output "Attempt $($attempt): Connecting to database..."
    Start-Sleep -Seconds 1
}

# 正确实例化Scripter对象
$scripter = New-Object Microsoft.SqlServer.Management.Smo.Scripter($srv)

$line="[cccauth].[spGetTrxReconSummary]"
$cleanLine = $line.Replace('[','').Replace(']','')
$splitLine = $cleanLine.Split(".")

$spObject = $db.StoredProcedures[$splitLine[1], $splitLine[0]]

Write-Host "Extracting stored procedure $($cleanLine)"
Write-Host "--------------------------------"
        
$spExtracted = $scripter.Script($spObject)
$spExtracted = $spExtracted.Replace("SET QUOTED_IDENTIFIER ON", "SET QUOTED_IDENTIFIER ON `r`nGO`r`n")

Write-Host $spExtracted -ForegroundColor Green

Write-Host "Scripting DB Finished... Press any key..."
[void][System.Console]::ReadKey($true)

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