通过Powershell调用存储过程时如何检索OUTPUT语句 [英] How to retrieve OUTPUT statement when calling stored proc through Powershell

问题描述

我在 powershell 中运行以下脚本，但是我似乎无法检索任何 PRINT 语句或错误消息?请问如何在 powershell 会话中捕获所有输出?

I am running the following script in powershell, however I don't seem to be able to retrieve any PRINT statements or error messages? How do I capture all outputs within the powershell session please?

$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
$SqlConnection.ConnectionString = "Server=$sql_server;Database=$sql_db;user ID=$sql_usr;password=$sql_pwd"
$SqlCmd = New-Object System.Data.SqlClient.SqlCommand
$SqlCmd.Connection = $SqlConnection
$SqlCmd.CommandText = "$storedProc"
$SqlCmd.CommandType = [System.Data.CommandType]::StoredProcedure
$SqlCmd.Parameters.Add("@COBDate", "$dateVariable")
$handler = [System.Data.SqlClient.SqlInfoMessageEventHandler] {param($sender, $event) Write-Host $event.Message }; 
$SqlConnection.add_InfoMessage($handler); 
$SqlConnection.FireInfoMessageEventOnUserErrors = $true;
$SqlConnection.Open()
$SqlCmd.ExecuteNonQuery()
$SqlCmd.Parameters.value
$SqlConnection.Close()

推荐答案

我实现Eventhandler的方式如下:

The way I've implemented Eventhandler is as follows:

#Method 1 use hidden method
$Sqlconnection.FireInfoMessageEventOnUserErrors=$true
#...
$handler = [System.Data.SqlClient.SqlInfoMessageEventHandler] {Write-Host "$($_)"}
$Sqlconnection.add_InfoMessage($handler)
#OR Method 2 use Register-ObjecEvent
Register-ObjectEvent -InputObject $SqlConnection-EventName InfoMessage -Action { Write-Host " $($Event.SourceEventArgs)" } -SupportEvent
#...
$SqlConnection.Open()

这篇关于通过Powershell调用存储过程时如何检索OUTPUT语句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！