Get SQL print message into a variable with ExecuteNonQuery() in Powershell

Question

I have this simple Powershell script that outputs the message "Hello World!" from SQL PRINT command:

$conn = New-Object System.Data.SqlClient.SqlConnection "Server=<server>;Database=msdb;Integrated Security=SSPI;";
$handler = [System.Data.SqlClient.SqlInfoMessageEventHandler] {param($sender, $event) Write-Host $event.Message};
$conn.add_InfoMessage($handler);
$conn.FireInfoMessageEventOnUserErrors = $true;
$conn.Open();
$cmd = $conn.CreateCommand();
$cmd.CommandTimeout=0
$cmd.CommandText = "PRINT 'Hello World!'";
$res = $cmd.ExecuteNonQuery();
$conn.Close();

I need to catch and parse this print output. How can I put it into a variable (in addition to just printing it out)?

Answer 1

I suggest reversing order: putting in a variable first, then printing it out:

$message = 'Hello World!'

$conn = New-Object System.Data.SqlClient.SqlConnection "Server=<server>;Database=msdb;Integrated Security=SSPI;";
$handler = [System.Data.SqlClient.SqlInfoMessageEventHandler] {param($sender, $event) Write-Host $event.Message};
$conn.add_InfoMessage($handler);
$conn.FireInfoMessageEventOnUserErrors = $true;
$conn.Open();
$cmd = $conn.CreateCommand();
$cmd.CommandTimeout=0
$cmd.CommandText = "PRINT $message";
$res = $cmd.ExecuteNonQuery();
$conn.Close();