Question:
I’ve created a sample Azure Automation Powershell Runbook. I’m trying to execute a SQL command and then print the messages from that command into Workbook output.
My code is taken from Capturing InfoMessage Output from SQL Server using PowerShell and it works if I run it locally:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 |
Write-Output "Starting" $conn = New-Object System.Data.SqlClient.SqlConnection "Data Source=abc.database.windows.net,1433;Initial Catalog=def;Integrated Security=False;User ID=ghj;Password=qwe" ## Attach the InfoMessage Event Handler to the connection to write out the messages $handler = [System.Data.SqlClient.SqlInfoMessageEventHandler] {param($sender, $event) Write-Output $event.Message }; $conn.add_InfoMessage($handler); $conn.FireInfoMessageEventOnUserErrors = $true; $conn.Open(); $cmd = $conn.CreateCommand(); $cmd.CommandText = "PRINT 'This is the message from the PRINT statement'"; $cmd.ExecuteNonQuery(); $cmd.CommandText = "RAISERROR('This is the message from the RAISERROR statement', 10, 1)"; $cmd.ExecuteNonQuery(); $conn.Close(); Write-Output "Done" |
After I run the workbook, I see Starting, -1 (from ExecuteNotQuery result) and Done but not messages from SQL.
Register-ObjectEvent from this answer doesn’t work either:
|
1 2 3 |
Register-ObjectEvent : Cannot register for the specified event. An event with the name 'InfoMessage' does not exist. Parameter name: eventName |
What am I doing wrong?
Answer:
It seems in Azure Automation there is a scope problem calling Write-Output or Write-Host inside of the handler. Setting the message to a global variable in the handler and then calling Write-Output after ExecuteNonQuery worked for my purposes.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 |
Write-Output "Starting" $conn = New-Object System.Data.SqlClient.SqlConnection "Data Source=abc.database.windows.net,1433;Initial Catalog=def;Integrated Security=False;User ID=ghj;Password=qwe" ## Attach the InfoMessage Event Handler to the connection to write out the messages $handler = [System.Data.SqlClient.SqlInfoMessageEventHandler] {param($sender, $event) $global:message = $event.Message}; $conn.add_InfoMessage($handler); $conn.FireInfoMessageEventOnUserErrors = $true; $conn.Open(); $cmd = $conn.CreateCommand(); $cmd.CommandText = "PRINT 'This is the message from the PRINT statement'"; $cmd.ExecuteNonQuery() | Out-Null; Write-Output $global:message $cmd.CommandText = "RAISERROR('This is the message from the RAISERROR statement', 10, 1)"; $cmd.ExecuteNonQuery() | Out-Null; Write-Output $global:message $conn.Close(); Write-Output "Done" |
If you are expecting more than one message you could concatenate them to the variable instead.
|
1 2 |
$handler = [System.Data.SqlClient.SqlInfoMessageEventHandler] {param($sender, $event) $global:message += $event.Message + "`n"}; |
However, in either case, they will not be raised when they are thrown, but after the query has finished.
EDIT: I found a solution that I like even better and wanted to share it. Use an object list in the handler…
|
1 2 3 |
$events = new-object System.Collections.Generic.List[Object] $handler = [System.Data.SqlClient.SqlInfoMessageEventHandler] { param($sender, $event) $events.Add($event) } |
…and after
ExecuteNonQuery loop to write all the messages|
1 2 3 4 5 |
ForEach($event in $events) { Write-Output $event.Message } |