Getting Messages back from SQL Server

I'm trying to get SQL query messages back into a C# 4.0 WinForm program using SQL Server 2008. Based on http://www.dotnetcurry.com/ShowArticle.aspx?ID=344 and http://geekswithblogs.net/mrnat/archive/2004/09/20/11431.aspx, the attached code should be right. But I put a breakpoint in the delegate and it is never fired. Ideas?
 
string connstr;
connstr = System.Configuration.ConfigurationManager.AppSettings["ConnectionString"];
SqlConnection conn = new SqlConnection(connstr);
conn.Open();
            
// Display messages
conn.InfoMessage += delegate(object sender, SqlInfoMessageEventArgs e) {
  stdmForm.txtLastSQLMessages.Text += "\n" + e.Message;
};

SqlCommand cmd = new SqlCommand();
cmd.CommandText = 
@"SELECT * FROM sys.databases WHERE database_id > 4
  PRINT 'XXX'
  RAISERROR('Message',10,25) WITH NOWAIT";
cmd.Connection = conn;
SqlDataReader rdr = cmd.ExecuteReader();

...

Open in new window

LVL 1
BlearyEyeAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

kamindaCommented:
Raiseerror will raise and error which will be available in your catch block of the code. So it is not a infoMessage.
0
BlearyEyeAuthor Commented:
So how do I get query messages back into my C# program?
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

BlearyEyeAuthor Commented:
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.
0
dbaSQLCommented:
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
0
Ted BouskillSenior Software DeveloperCommented:
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.
0
MlandaTCommented:
The InfoMessage event will normally fire only for informational and warning messages that are sent from the server. However, when an actual error occurs, the execution of the ExecuteNonQuery or ExecuteReader method that initiated the server operation is halted and an exception is thrown.

If you want to continue processing the rest of the statements in a command regardless of any errors produced by the server, set the FireInfoMessageEventOnUserErrors property of the SqlConnection to true. Doing this causes the connection to fire the InfoMessage event for errors instead of throwing an exception and interrupting processing. The client application can then handle this event and respond to error conditions.
http://msdn.microsoft.com/en-us/library/a0hee08w%28v=VS.100%29.aspx



0
Vitor MontalvãoMSSQL Senior EngineerCommented:
Why don't you run that SQL statement inside a TRY ... CATCH block?
0
dbaSQLCommented:
0
BlearyEyeAuthor Commented:
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?
0
Ted BouskillSenior Software DeveloperCommented:
The stuff that shows up in the Messages tab is what is received in the InfoMessage events.  They are one and the same.
0
BlearyEyeAuthor Commented:
Sweet. I'll take a look at it.
0
BlearyEyeAuthor Commented:
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

Open 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();
        }

Open in new window

0
Vitor MontalvãoMSSQL Senior EngineerCommented:
TRY...CATCH is to use in your C# code. dbaSQL already sent you two links about that.
0
MlandaTCommented:
Try...catch
If you're ADO.NET 2.0, you can use the FireInfoMessageEventOnUserErrors property. When it's set to true, two things change:

- info messages (prints) are informed to event handlers immediately
- errors are reported through the event, instead of through an exception in the Execute methods

So once you set FireInfoMessageEventOnUserErrors  = true, the try catch block may not catch the errors per ce.
Code
Will look at your code and see why it's perhaps not working.... It all looks correct and it should work.
Purpose
However, also looking at your stored procedure... I think we may perhaps need to go back to the real reason for what you are trying to achieve here....??? There could also be simple alternative implementations
0
BlearyEyeAuthor Commented:
The real procedure will be doing a bunch of stuff and I want to give progress/status messages back to the user.
0
Ted BouskillSenior Software DeveloperCommented:
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.

0
BlearyEyeAuthor Commented:
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.
0
BlearyEyeAuthor Commented:
Any thoughts?
0
Ted BouskillSenior Software DeveloperCommented:
Try the following change
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;
            SqlCommand cmd = new SqlCommand("aaaTemp",conn);
            cmd.CommandType=CommandType.StoredProcedure;
            conn.Open();
            SqlDataReader rdr = cmd.ExecuteReader();
            rdr.Read();
            string x = (string)rdr["PatientPIN"];

...

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

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
BlearyEyeAuthor Commented:
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.
0
Ted BouskillSenior Software DeveloperCommented:
Not all of the messages are returned.  They have to be a specific level but I can't remember the range.
0
BlearyEyeAuthor Commented:
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#.
0
BlearyEyeAuthor Commented:
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

Open 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";
        }
    }

Open 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

Open in new window

0
Ted BouskillSenior Software DeveloperCommented:
You are still opening the connection too soon.  You don't have to use Open until the line before the ExecuteNonQuery()
0
BlearyEyeAuthor Commented:
OK. Does it matter? I seem to be getting what I'm looking for ...
0
Ted BouskillSenior Software DeveloperCommented:
It won't affect the messaging but it's a recommended best practice from Microsoft for performance.  Cheers
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
.NET Programming

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.