Getting Messages back from SQL Server

Raiseerror will raise and error which will be available in your catch block of the code. So it is not a infoMessage.

So how do I get query messages back into my C# program?

Have you seen either of these?:
http://weblogs.asp.net/andrewrea/archive/2008/02/19/examples-of-using-system-data-parameterdirection-with-sql-server.aspx
http://forums.asp.net/t/1208409.aspx/1?Interesting+problem+with+getting+OUTPUT+parameters+from+SQL+Server+using+C+

hmmm ... I know I can get output parameters back, but what I'm really looking for here is a way to get the query messages back. In SQL Server Management Studio, that's what shows up in the "Messages" tab along side the "Results" tab.

I haven't actually done it, but I believe you're able to do this with CLR procedures:
http://msdn.microsoft.com/en-us/library/ms131094.aspx

Those info messages are actually expensive.  If you create a stored procedure with SET NOCOUNT OFF then the number of records changed in an operation will be returned as an info message.  However, Microsoft recommends SET NOCOUNT ON to avoid that situation to speed up the transaction.

I've only had success with SqlInfoMessage's when I use stored procedures.

Also, try opening the connection AFTER you add the event handler.  The rule of thumb that Microsoft recommends for ADO.NET is to open late/close early for all SQL connections.

Why don't you run that SQL statement inside a TRY ... CATCH block?

Yes, VMontalvao, I think the TRY/CATCH block is a good idea:
http://msdn.microsoft.com/en-us/library/ms166340.aspx
https://www.experts-exchange.com/questions/24133842/SQL-Server-C-using-over-try-catch-block-advatages.html

Sorry for the delay. It looks like I should be able to get InfoMessage events but not the stuff that shows up in the Messages tab, right?

The stuff that shows up in the Messages tab is what is received in the InfoMessage events.  They are one and the same.

Sweet. I'll take a look at it.

Here's test code I put together along with a stored procedure. The InfoMessage event is never fired.

I didn't get the try ... catch idea. What exception would I be catching?

USE [STUV3_2_Testing2]
GO
/****** Object:  StoredProcedure [dbo].[aaaTemp]    Script Date: 04/16/2011 08:13:47 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[aaaTemp]
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

select * from Patient;
print 'We got something here';

END

Select allOpen in new window

string connectionStr = @"Data Source=(local)\SQLEXPRESS;Initial Catalog=STUV3_2_Testing2;Integrated Security=True;";
            SqlConnection conn = new SqlConnection(connectionStr);
            conn.InfoMessage+= new SqlInfoMessageEventHandler(conn_InfoMessage);
            conn.FireInfoMessageEventOnUserErrors = true;
            conn.Open();
            SqlCommand cmd = new SqlCommand("aaaTemp",conn);
            cmd.CommandType=CommandType.StoredProcedure;
            SqlDataReader rdr = cmd.ExecuteReader();
            rdr.Read();
            string x = (string)rdr["PatientPIN"];

...

        private static void conn_InfoMessage(object theSender, SqlInfoMessageEventArgs theEvent) {
            string y = theEvent.Errors[0].ToString();
        }

Select allOpen in new window

TRY...CATCH is to use in your C# code. dbaSQL already sent you two links about that.

The real procedure will be doing a bunch of stuff and I want to give progress/status messages back to the user.

Relaying messages back from SQL server will slow your application.  That is why in the stored procedure templates Microsoft includes the statement 'SET NOCOUNT ON;' which turns off the messages to insert/update/delete each row in a statement.

I wouldn't recommend it as a way to keep your users informed.  I only use SqlInfoMessages for error handling or debugging.  I don't use them to keep users informed.

In this case, speed isn't much of an issue. This procedure is run by an admin, and that rarely. There will likely be fewer than 10 messages total. The overhead for getting info messages back will be trivial  So I'd still like to be able to do this.

Any thoughts?

I posted a reply a while back but must have forgotten to click on Submit ...

tedbilly: I tried the change you suggested. It gives me errors (such as not finding the stored procedure) but not the system messages.

Not all of the messages are returned.  They have to be a specific level but I can't remember the range.

I want to be able to give progress messages to the user. Is there some way I can perhaps simulate an error in order to get a message back to the C# program? Something parallel to "throw new exception(...)" in C#.

Actually, it seems to be working now; I needed to use ExecuteNonQuery() ...  attached is the C# code, the SQL code, and the output. If it's a query that's handled by SQLReader (or presumably by a dataset), then only the messages before the SELECT  are sent to the program.

In my case, non-query is what I need, so this should work out fine.

System.Data.SqlClient.SqlError: test1
System.Data.SqlClient.SqlError: Error1
System.Data.SqlClient.SqlError: test2
System.Data.SqlClient.SqlError: Error2

Select allOpen in new window

private void Form1_Load(object sender, EventArgs e) {
            string connectionStr = @"Data Source=(local)\SQLEXPRESS;Initial Catalog=STUV3_2;Integrated Security=True;";
            using (SqlConnection conn = new SqlConnection(connectionStr)) {
                conn.InfoMessage += new SqlInfoMessageEventHandler(conn_InfoMessage);
                conn.FireInfoMessageEventOnUserErrors = true;
                conn.Open();
                SqlCommand cmd = new SqlCommand("aaaTest", conn);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.ExecuteNonQuery();
            }
        }

        private void conn_InfoMessage(object theSender, SqlInfoMessageEventArgs theEvent) {
            string y = theEvent.Errors[0].ToString();
            textBox1.Text += theEvent.Errors[0].ToString() + "\r\n";
        }
    }

Select allOpen in new window

ALTER PROCEDURE [dbo].[aaaTest] 
AS
BEGIN
	print 'test1'
	RAISERROR('Error1',10,26) WITH NOWAIT
	print 'test2'
	RAISERROR('Error2',12,27) WITH NOWAIT
END

Select allOpen in new window

You are still opening the connection too soon.  You don't have to use Open until the line before the ExecuteNonQuery()

OK. Does it matter? I seem to be getting what I'm looking for ...

It won't affect the messaging but it's a recommended best practice from Microsoft for performance.  Cheers