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?

It seems for me this command is to execute commands in sqlserver `$cmd.CommandText` — TheGameiswar, Jan 29 '18 at 10:00
you should use `Write-Output`some thing like this `Write-Output "Output inside of function"` — TheGameiswar, Jan 29 '18 at 10:01
Yes, I'm trying to execute commands in SQL Server and I do use `Write-Output` in the code above — Mikhail Shilkov, Jan 29 '18 at 10:01
You are saying `but not messages from SQL` i dont see any reason why they should be printed in output pane — TheGameiswar, Jan 29 '18 at 10:04
If you want messages from sql, one way would be to assign the output of cmd.execute non query or use a stored proc with some output parameter and assign it to output object — TheGameiswar, Jan 29 '18 at 10:05
The links that I reference say how `SqlInfoMessageEventHandler` is used to handle info events and I call `Write-Output` from inside the handler. Stored procedure is given and it has no output parameters, it just does `PRINT` calls. The output of `ExecuteNonQuery` is `-1` — Mikhail Shilkov, Jan 29 '18 at 10:07

Andy Funk · Accepted Answer · 2018-02-06T18:16:24.620

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
}

Fantastic. I was pulling my hair out with Azure until I finally found this answer. — Uber Schnoz, Jun 15 '18 at 16:19
Anybody knows why Write-Output would not work? Would be great to get those outputs real time. — LucasM, Jun 17 '18 at 06:08
That work's fine, but it make the runbook much slower, without logging runtime is about 1 minute, with logging it's up to 1 hour. — user2431419, Aug 05 '22 at 13:24

score 0 · Answer 2 · answered Jan 29 '18 at 11:08

0

Here's the code I use for getting print statements:

$connection = New-Object System.Data.SqlClient.SqlConnection
$connection.ConnectionString = "Server=..."

try {
    $connection.Open()

    $command = $connection.CreateCommand()
    $command.CommandText = "PRINT 'Hello World!';"

    Register-ObjectEvent -InputObject $connection -EventName InfoMessage -Action { Write-Host "$($event.SourceEventArgs)" } -SupportEvent

    $command.ExecuteNonQuery() | Out-Null
}
catch {
    throw $_.Exception.ToString()
}
finally {
    $connection.Dispose()
}

answered Jan 29 '18 at 11:08

gvee

16,732
35
50

Are you running it from Azure Automation? Where do you see the text printed by `Write-Host`? Your code doesn't print anything in my Automation Runbook (neither with `Write-Output`) – Mikhail Shilkov Jan 29 '18 at 11:21
@Mikhail in your code you are (currently) assigning the output of the `ExecuteNonQuery` to a variable; so Write-Host/Output is likely to be "swallowed" by that. – gvee Jan 29 '18 at 11:27
Just changed it not to assign to anything. It prints `-1` two times, nothing more. Changed my sample accordingly. – Mikhail Shilkov Jan 29 '18 at 11:37

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

2 Answers2