WITH NOWAIT And SqlConnection's InfoMessage

  • Thread starter Thread starter Patrick
  • Start date Start date
P

Patrick

Is there any possible way to get messages asynchronously sent from a
stored procedure running on SQL Server 2000, back to ADO.Net, in
realtime (or at least somewhat close to it)?

After much experimenting, I am left to say "no"... it is not possible.
Although it would appear it should be.

On SQL Server, the RAISERROR command has an available "WITH NOWAIT"
option. According to the documentation, this "sends messages
immediately to the client".

This, combined with the SqlConnection object's InfoMessage event,
should theoretically allow one to receive these messages
asynchronously, while the proc is executing. I assume this doesn't
work because ExecuteNonQuery does not fire this event until after it
(and thus the stored procedure) has completed.

Here's some sample code... first my stored proc:

RAISERROR('1',10,1) WITH NOWAIT
RAISERROR('2',10,1) WITH NOWAIT
RAISERROR('3',10,1) WITH NOWAIT

And the C# code:

private void RunProc()
{

this.listBox1.Items.Add("Proc started...");

try
{
this.sqlConnection1.Open();
this.sqlCommand1.ExecuteNonQuery();
}
finally
{
this.sqlConnection1.Close();
}

this.listBox1.Items.Add("Proc complete...");

}

private void sqlConnection1_InfoMessage(object sender,
System.Data.SqlClient.SqlInfoMessageEventArgs e)
{
this.listBox1.Items.Add(e.Message);
}
 
Unfortunately, that's was a limitation in .NET 1.0 and 1.1 and there are no
work-arounds other than changing the way of doing it (e.g. instead of saying
PRINT 'blah' you can say SELECT 'blah' and process the message as a
result-set in the client).

In .NET 2.0 we fixed that. However, we didn't want to change the default
behavior to avoid potentially breaking existing applications, so in order to
get the new behavior to have to enable it. By enabling this you also get
another change: errors are also reported in the InfoMessage event, not
through exceptions thrown.

The property is SqlCommand.FireInfoMessageEventOnUserErrors and you need to
set it to true (yep, the name is a bit long...)

Docs for the property are here (watch for line breaks):
http://msdn2.microsoft.com/en-us/li...nection.fireinfomessageeventonusererrors.aspx

--
Pablo Castro
Program Manager - ADO.NET Team
Microsoft Corp.

This posting is provided "AS IS" with no warranties, and confers no rights.
 
Back
Top