Showing progress while sp is running, possible ?

[Setya]

Hi guys,
I have a stored procedure that involves intensive record looping so that it takes hours to finish. When I call it from my VB apps, all I can do to notify users that my apps is running is just by changing the cursor into Hourglass and as you might guess this ain't enough. Users might still wonder whether my app hangs.
During the execution, is it possible for the sp to send some kind of progress message to the front end ? I know this may cause heavy network traffics. But since it doesn't contain any data, I think it's still acceptable.

Thanks in advance

Setya

 

[tlbroadbent]

You'll need to use asynchronous queries (availble in ADO and RDO) so the VB program can execute code to monitor progress while the stored procedure executes. You'll want to become familiar with the asynchronous properties, methods and events in ADO or RDO.

Check the following articles for techniques. You may find something you can use depending on how you connect to SQL Server.

http://support.microsoft.com/support/kb/articles/Q262/3/11.ASP

http://images.examnotes.net/mcsd/70-175.shtml

http://images.examnotes.net/mcsd/70-176.shtml

http://www.elementkjournals.com/ivb/9808/ivb9881.htm

Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[balves]

You could create a table with the name of the stored proc and its estimated completion time (either hard-coded or averaged after each run of a proc). Then use that data to create a progress bar on the VB form that crawls along as the elapsed time moves toward the estimated completion time.

Or..one thing I have done is create a dialog box that pops up while the stored proc is running. It has a counter of elapsed time and a counter of records processed (e.g, figured out by counting the number of records created in table X)

 

[Terpsfan]

All of the above ideas are good but one thing you could do is base the progress bar on the number of records you are handling. The max value of the progress meter would be the record count. The value would be the absolute position of the recordset or a bookmark value. The minimum value would be zero and you would update it however often you want. I use this when I'm processing large recordsets.