Infomessage event problem

Hi,

I have a long running stored procedure in which I have placed raiserrors
to track progress in real time. It works great in query analyzer.
I am trying to provide real time progress to the client using the
Infomessage event handler and provide a method that does this. However, the
Infomessage seems to only work AFTER the stored procedure is finished and it
outputs all the raised errors all at once. Is this a bug?

thanks in advance

Mary
Fri Feb 18 17:51:26 CST 2005

No, it's not a bug. When you call the stored procedure from your
client app, commands are sent over the network in a single call to be
processed on the server, which speeds up execution since all the work
is taking place on the server. When the stored procedure completes, it
sends back any result sets and/or any output parameter values to the
client. The stored procedure would need to be in continuous
communication with the client, employing multiple round trips across
the network for each infomessage. When you think about thousands of
users running a stored procedure all at the same time, then you
realize that if it did work that way that the network would be bogged
down in short order. HTH,

Mary

On Fri, 18 Feb 2005 13:45:03 -0800, "Homer"
<Homer@discussions.microsoft.com> wrote:

>Hi,
>
> I have a long running stored procedure in which I have placed raiserrors
>to track progress in real time. It works great in query analyzer.
> I am trying to provide real time progress to the client using the
>Infomessage event handler and provide a method that does this. However, the
>Infomessage seems to only work AFTER the stored procedure is finished and it
>outputs all the raised errors all at once. Is this a bug?
>
>thanks in advance

Sahil
Sat Feb 19 02:07:45 CST 2005

No dude it ain't a bug. It's due to performance reasons that those are
batched together. Your best bet is to rearchitect the stored proc and split
it into smaller tasks in repeated calls - and then show the progress bar
based upon that. Obviously a progress bar solution means multiple database
hits and hence lower performance.

- Sahil Malik
http://codebetter.com/blogs/sahil.malik/


"Homer" <Homer@discussions.microsoft.com> wrote in message
news:103F95D5-6184-4989-BC2E-F4B6F5854EE7@microsoft.com...
> Hi,
>
> I have a long running stored procedure in which I have placed raiserrors
> to track progress in real time. It works great in query analyzer.
> I am trying to provide real time progress to the client using the
> Infomessage event handler and provide a method that does this. However,
> the
> Infomessage seems to only work AFTER the stored procedure is finished and
> it
> outputs all the raised errors all at once. Is this a bug?
>
> thanks in advance

Pablo
Mon Feb 21 21:25:21 CST 2005

Sorry to contradict everyone on this one :)

I'm not sure I'd call it strictly a "bug", let's just say it's "undesirable
behavior" :)

I agree that for certain scenarios where you need to report progress this
would be a nice thing to have. In the next version of the .NET Framework
you'll have the option to get InfoMessage events as the server sends them to
the client, instead of all of them at the end.

Unfortunately there is no work-around for the current versions.

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

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


"Homer" <Homer@discussions.microsoft.com> wrote in message
news:103F95D5-6184-4989-BC2E-F4B6F5854EE7@microsoft.com...
> Hi,
>
> I have a long running stored procedure in which I have placed raiserrors
> to track progress in real time. It works great in query analyzer.
> I am trying to provide real time progress to the client using the
> Infomessage event handler and provide a method that does this. However,
the
> Infomessage seems to only work AFTER the stored procedure is finished and
it
> outputs all the raised errors all at once. Is this a bug?
>
> thanks in advance