Using the result of the SQL "Messages" pane

Question

I have an existing SQL 2005 stored procedure that for some reason, outputs its results in the Messages pane in SSMS instead of the Results pane. (Its actually a CLR procedure already compiled and deployed to all our servers, and used for another daily process. So I can't change it, I just want to use its output.)

For the sake of discussion, here's a stored proc that behaves the same way:

CREATE PROCEDURE [dbo].[OutputTest] 
    @Param1  int, @Param2 varchar(100)
AS
BEGIN
    SET NOCOUNT ON;
    PRINT 'C,10000,15000';
    PRINT 'D,30000,90000';
    PRINT 'E,500,50000';
END

So no actual SELECT statement in there, and if you run this, you'll see these results only on the Messages pane.

Is there any way for me to use these results as part of a larger query? Put them in a temp table or something, so I can parse them out?

None of the "normal stuff" works, because there is no true "output" here:

INSERT INTO #output
EXEC OutputTest 100, 'bob'

just shows

C,10000,15000
D,30000,90000
E,500,50000

(0 row(s) affected)

on the messages pane, and the temp table doesn't actually get anything put into it.

Answer 1

I don't think there is a way but even if there is I think you should seriously consider whether it is a good idea. This sounds like a fudge which can only cause you pain in the long term. Creating an alternative proc that does exactly what you want sounds to me like a better plan.

Answer 2

there is no way to get messages from the message pane in your result. if you think about it the SSMS is just a client that parses those messages the way you see it.

if you wan to use them in your app take a look at Connection Events in ADO.NET

Answer 3

The only way I could think that this might be possible is if the output is printed via the RAISERROR command. In that case, you might be able to capture it elsewhere using TRY/CATCH.

But that's just an idea: I've never done it. In fact, the only thing we do that's remotely close is that we have a command line tool to run stored procedures in batch jobs rather than using sql server agent to schedule them. This way all of our nightly jobs are scheduled in one place (the windows task scheduler) rather than two, and the command line tool captures the anything printed to the message window into a common logging system that we monitor. So some of procedures will output quite a lot of detail to that window.

Answer 4

Can you execute the stored proc from C# code? If so, you might be able to hook into the SqlCommand event called SqlInfoMessage:

SqlConnection _con = new SqlConnection("server=.;
            database=Northwind;integrated Security=SSPI;");

_con.InfoMessage += new SqlInfoMessageEventHandler(_con_InfoMessage);

The event handler will look like this:

static void _con_InfoMessage(object sender, SqlInfoMessageEventArgs e)
{
    string myMsg = e.Message;            
}

The "e.Message" is the message printed out to the message window in SQL Server Mgmt Studio.

While it won't be pretty and might require some ugly parsing code, at least you could get a hold of those messages that way, I hope!

Marc

Answer 5

You cannot trap, catch or use these messages from within SQL Server. You can, however, receive them from within a client application.