SqlInfoMessageEventHandler/RAISERROR caching?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I'm trying to get progress messages from a long-running stored proc to a C#
client program. In the proc, I use RAISERROR (@Message,10,1) WITH NOWAIT",
and I've set up a SqlInfoMessageEventHandler. The handler gets all messages
at the end of the proc, not as they are generated. However, when I run the
proc from Query Analyzer, I get the messages right away. Is there any
buffering going on that I can flush?
 
When you call a stored procedure from a client app, it does it's
processing, and then returns all results in one go, not piecemeal.
IOW, does not hold state while returning information piecemeal. Create
a Profiler trace to get a greater understanding of what's going on
under the hood between your app and SQLS.

--Mary
 
BUT when I run the proc from Query Analyzer, I *do* get the RAISERROR
messages piecemeal. I just don't get them piecemeal from the C# client.
There seems to be some different handling of the connection between Query
Analyzer and the dot net framework.

Try from Query Analyzer:

RAISERROR('Message A',10,1) WITH NOWAIT
WAITFOR DELAY '00:00:05'
RAISERROR('Message B',10,1) WITH NOWAIT
 
What you are seeing in QA is that each of the statements below is
executed as a separate batch, meaning three separate calls as each is
executed in the QA window. When you send those three statements from
your .NET client, they are passed to the server as one batch, where
the engine attempts to optimize them and provide the most efficient
query plan possible. Any result sets from a SELECT statement are
passed back in one call, with the done in proc message (rows affected)
as a second call, which is why you always want SET NOCOUNT ON as the
first statement in your stored procedures as it elminates this second
trip across the wire. QA is a specialized client tool for debugging
T-SQL, among other things, and you wouldn't want the same behaviors in
other clients because it would be horribly inefficient and slow. If
you want to send back each RAISERROR separately, then wrap each in a
separate SELECT statement and use the .NextResult method to fetch
them. However, I wouldn't do this in a production environment as
you'll negatively impact performance and scalability.

--Mary
 
Back
Top