Capturing InfoMessage Output (PRINT, RAISERROR) from SQL Server using PowerShell

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.

12 thoughts on “Capturing InfoMessage Output (PRINT, RAISERROR) from SQL Server using PowerShell

  1. Hi,

    Thanks for the example but how to I retrieve the messages while the execution is happening, rather then simple after all execution is done?

  2. Hi,

    Thanks for the example but how do I retrieve the messages while the execution is happening, rather then all statements being shown after the execution is complete?

  3. I had a requirement to execute the stored procedure from Powershell and I wanted to see the print Statements. This post has fulfilled my requirement.

    Thanks a lot.

    Cheers,
    Venu

    1. The messages are being sent over the TDS stream already anyways, so the network impact to the session is already there. Handling the message is relatively low in performance impact. If you stopped the messages from going over TDS it would reduce the packet count and could have a slight performance improvement, that is why SET NOCOUNT ON is recommended.

  4. This was a life saver. Helps a person see messages like “You must be a sysadmin to use this stored procedure”. (╯°□°)╯︵ ┻━┻

  5. I’m trying to use this to display the rows affected to the console (like ssms messages) doesn’t ExecuteNonQuery() contain this info? How do I extract it?
    Thanks -Eric

    1. It depends. If someone changed the server configuration to set no count to ON then it wouldn’t:

      EXEC sys.sp_configure N’user options’, N’512′
      GO
      RECONFIGURE

      I don’t suggest doing that, but you might check it to be sure.

Leave a Reply

Your email address will not be published. Required fields are marked *

Other articles

Imagine feeling confident enough to handle whatever your database throws at you.

With training and consulting from SQLskills, you’ll be able to solve big problems, elevate your team’s capacity, and take control of your data career.