Progress Bar & Stored Procedure

[MDA]

Greetings,
I have a simple VB6 application that executes a Stored Procedure on SLQ Server 2000. The procedure copies anywhere from 10,000 to ++Millions of transactions, which can take from a few minutes to close to an hour. I would like to display a progress bar to the user as a status update. My idea is to first calculate the number of transactions to be processed. Next execute the SP that has a return value every say 100 or 1000 transactions to VB. My form will display a status bar of % complete. The other option is to return every record but I assume this will put a load on network traffic and not accepted by most networks admins??

I am somewhat of a beginner so any/all help is greatly appreciated. What would you do to accomplish this business requirement?

Thanks in advance for any help
Regards,
Mike

 

[fredericofonseca]

Unless you need EVERY record returned to you then don't even try to do it. It will slow down the process.

So the main question here is "Do you need the resulting records returned to you?"

If you do then you will need to first know how many records will be returned. Don't know about your case, but this sometimes is not possible without running the FULL SQL which means it's not feasible to do so.

So assuming you have the count and you need all your records see thread222-400785

basically you do.

sql.execute

while not EOF
do RS stuff
if count_of_records > 10000 ' for less than this you may not need a progress bar. Adjust at will
if records_read > 1000 ' or another value
update progress bar
doevents
move zeros to records_read
end if
end if
add 1 to records_read
wend


Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com