Question:
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?
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
$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() |
Answer:
The way I’ve implemented Eventhandler is as follows:
|
1 2 3 4 5 6 7 8 9 10 |
#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() |