Show same stored proc verbose output in a .NET that you would see in the Query Analyzer tool

Question

Hello,

The DBA displays a lot of info in his stored proc as it executes.

From a Visual Studio 2003 CSharp Windows Form, when you launch a SQL Server 2000 stored procedure that returns records as well as an OUTPUT param, is it possible to display the same verbose output as you would see in the Query Analyzer tool?

This is on top of showing the returned data in the datagrid.

Thanks, Bert

Answer 1

I am assuming it is messages from print statements you are after. These can be picked up from the SqlConnection.InfoMessage event. Example code:

SqlConnection conn = new SqlConnection("...");
conn.InfoMessage += new SqlInfoMessageEventHandler(RecieveInfoMessage);

Assuming that you have a method like this declared nearby:

void RecieveInfoMessage(object sender, SqlInfoMessageEventArgs e)
{
    Console.WriteLine(e.Message);
}

You are of course free to do whatever you want with the message - write to console, write to stream/file, append to StringBuilder for later display in a form, etc.

Answer 2

In addition to what Jorn said, mind, that without setting FireInfoMessageEventOnUserErrors property to true, the messages will only be printed after the procedure has finished.

For long running stuff this might not be what you expect as all "progress" messages are only printed after the thing is done - so the behavior is not as you would see it in SMSS or with SQLCMD.EXE.

On the other hand if FireInfoMessageEventOnUserErrors is set to true, error handling must be done manually.

Check here for further information.