Tonight, a question was asked on the #sqlhelp tag on Twitter about how to capture all of the output from a stored procedures execution, to include the informational and error message outputs that may be returned by using PRINT or RAISERROR in the code.  This was a problem I ran into in the past with C# and the way you do it in .NET is to handle the InfoMessage Event for the SqlConnection object using a SqlInfoMessageEventHandler in your code.  The same thing can be done in PowerShell to have these messages written out to the output of a scripts execution.

$conn = New-Object System.Data.SqlClient.SqlConnection "Server=.\LAB1;Database=tempdb;Integrated Security=SSPI;";
$conn.Open();

## Standard default connection with no EventHandler
$cmd = $conn.CreateCommand();
$cmd.CommandText = "PRINT ‘This is the message from the PRINT statement’";
$res = $cmd.ExecuteNonQuery();
$cmd.CommandText = "RAISERROR(‘This is the message from the RAISERROR statement’, 10, 1)"; 
$res = $cmd.ExecuteNonQuery();
$conn.Close();

## Attach the InfoMessage Event Handler to the connection to write out the messages
$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.CommandText = "PRINT ‘This is the message from the PRINT statement’";
$res = $cmd.ExecuteNonQuery();
$cmd.CommandText = "RAISERROR(‘This is the message from the RAISERROR statement’, 10, 1)"; 
$res = $cmd.ExecuteNonQuery();
$conn.Close();

The bold code region above creates the EventHandler so that it writes the Message out to the host and then adds the handler to the InfoMessage Event for the connection.  The last line of code specifies for the event to fire on lower priority user errors as well.