use progress bar to show status of SQL stored procedure action

[MDA]

Hi all,
I have a small VB app that executes a stored procedure in SQL Server. This stored procedure can take from 1 min to ++hours depending on the number of transactions in the database. Is there a way to link a progress bar to the number of transactions completed by the stored procedure?

I can count the transactions and estimate a time of completion but a status (progress) bar will assure the user that the thing is still running and graphically show how much time to go!

Any code help or ideas is greatly appreciated!

Regards,

Mike

 

[RayMan]

jUST AN IDEA

Since the stored procedure takes control and does not pass control back to your app until finished, I do not think you have the ability to increment your progress bar. If you modify your stored procedure to do N transactions and then return to the Vb app then you can increment the progress bar. You can continue to call the stored procedure until all transactions have been processed. You will need to commit at the end of each interation of the stored procedure. You will probably need to do a select count(*) to determine the number of transactions to be processed. this will be the high water mark for your progress bar.
A select count(*) will tend to run very quickly and give you the high water mark you need (at least it does on Oracle)

RayMan

 

[Ruairi]

Note: for some reason @ show up as @ in code. sorry

If you can estimate the total number of times through a main loop in the sp then you could do something like this.
In the main loop of your stored procedure increment a variable each time through.

declare @NumDone int
declare @strDone varchar(15) --however many characters you may need.

select @NumDone = 0 

while (whatever your loop conditions are) 
  begin 
   --do stuff
   select @NumDone = @NumDone + 1 
   select @strDone = convert(varchar (15), @NumDone) 
   raiserror(@strDone, 1, 1) 
  end

Then back in vbland set up some way to keep checking the .errors.count property of the connection object. If it is > 1 Then

CurrentProgress = cn.errors(cn.errors.count - 1).Description
NumCurrentProgress = Val(CurrentProgress) 
ProgressBar.Value = NumCurrentProgress 
ProgressBar.Refresh

The only problem i forsee with this is that i don't think control returns to your vb program until the sp finishes. For that reason you may need to pass your connection object ByRef and the estimated Number of iterations( make sure you estimate high) to an external .dll that knows how to check kthat connection for progress and draw a progress bar( of course you'd have to write that .dll first).
HTH Ruairi

Could your manufacturing facility benefit from real time process monitoring? Would you like your employees to be able to see up to the minute goal and actual production?
For innovative, low cost solutions check out my website.

http://www.plccontroltechnologies.com