Printing messages from SQL in Azure Automation powershell script doesn't work

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:

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:

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 1

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.

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.

$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...

$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

ForEach($event in $events)
{
    Write-Output $event.Message
}