sql-server - 在 Powershell 中使用 ExecuteNonQuery() 将 SQL 打印消息获取到变量中

Question

我有这个简单的 Powershell 脚本，它输出消息“Hello World！” 从 SQL PRINT 命令：

$conn = New-Object System.Data.SqlClient.SqlConnection "Server=<server>;Database=msdb;Integrated Security=SSPI;";
$handler = [System.Data.SqlClient.SqlInfoMessageEventHandler] {param($sender, $event) Write-Host $event.Message};
$conn.add_InfoMessage($handler);
$conn.FireInfoMessageEventOnUserErrors = $true;
$conn.Open();
$cmd = $conn.CreateCommand();
$cmd.CommandTimeout=0
$cmd.CommandText = "PRINT 'Hello World!'";
$res = $cmd.ExecuteNonQuery();
$conn.Close();

我需要捕捉并解析这个打印输出。我怎样才能把它放入一个变量中（除了打印出来）？

标签： sql-serverpowershell

Answer 1

您应该将 的值分配给$event.Message全局变量$global:msg（或以 开头的其他名称$global:）。然后这个变量将在事件处理程序范围之外是可访问的：

$conn = New-Object System.Data.SqlClient.SqlConnection "Server=<server name>;Database=msdb;Integrated Security=SSPI;";
$global:msg = ""
$handler = [System.Data.SqlClient.SqlInfoMessageEventHandler] {
    param($sender, $event) 
    $global:msg = $event.Message
    Write-Host $global:msg
};
$conn.add_InfoMessage($handler);
$conn.FireInfoMessageEventOnUserErrors = $true
$conn.Open();
$cmd = $conn.CreateCommand();
$cmd.CommandTimeout=0
$cmd.CommandText = "PRINT 'Hello World!'";
$res = $cmd.ExecuteNonQuery();
$conn.Close();
Write-Host "The event handler message '$global:msg' is inside of the global variable!"