Getting information messages back in real-time

Hello,

It's possible to create messages in SQL Server 2000 that get sent to
the client immediately, by using RAISERROR ... WITH NOWAIT. For
example, the following code works great in Query Analyzer:


RAISERROR(N'Message 1', 0, 1) WITH NOWAIT
WAITFOR DELAY '00:00:02'

RAISERROR(N'Message 2', 2, 1) WITH NOWAIT
WAITFOR DELAY '00:00:02

RAISERROR(N'Message 3', 4, 1) WITH NOWAIT
WAITFOR DELAY '00:00:02'

However, I find that when I try to execute a script or procedure from
ADO.NET, and try to receive these messages using the InfoMessage
delegate, I don't get them until the very end, all in one chunk. I'm
writing a long-running procedure, and I'd like the client to be able
to receive status messages to print to the console. Is there any way
to get messages to get sent to the client in real-time?

Thanks,
Brian


Posted Via Usenet.com Premium Usenet Newsgroup Services
----------------------------------------------------------
** SPEED ** RETENTION ** COMPLETION ** ANONYMITY **
----------------------------------------------------------
http://www.usenet.com

brian
Mon Jul 12 18:03:09 CDT 2004

In case anyone else stumbles across this issue, I've found the answer.
For some reason, if you execute SQL using the method
SqlCommand.ExecuteNonQuery, all server output is batched up and
returned in a single call to the InfoMessage delegate.

However, if you use any of the other Execute methods (ExecuteScalar,
ExecuteReader, and ExecuteXmlReader), the InfoMessage delegate will
be called once for every server info message. The calls are not quite
real-time - they tend to be spaced out at random intervals of between
30 and 60 seconds, but it's better than getting all the messages back
at the end of a 30 minute procedure.

Hope that helps!
Brian