Indicating Stored Procedure progress on Form

[Alan Z. Scharf]

Hi,

1. I have a master stored procedure that runs a series of other SP's in
succeession.
2. At the end of each individual SP, I have a command that writes a record
to a table with the completed SP's name and elapsed time.
3. I want to show this table on a form with the records being added one by
one as each SP completes.

4. The table-writing methood itself works, but I can't get the form to
requery the underlying form query on this table to display each record as
it
is added.
5. I tried using the form's OnTimer event together with an interval of
2000,
but nothing happens,

When the master SP completes, the form requeries and the completed
'results'
table displays with all records at once.

Question:
---------

Is there a mthod to get this to work? Am I mising something?

Thanks.

Alan

 

[Mary Chipman [MSFT]]

When you execute a stored procedure that calls other stored
procedures, all of that code is executing on the server. There are no
callbacks to the client that would allow you to time each individual
stored procedure. You'd have to call each stored procedure
individually, which would result in n round trips as each invocation
was processed separately. I'm assuming that the purpose of this is
transactional consistency, which you would lose if you implemented
each one as a separate call.

--Mary

 

[Alan Z. Scharf]

ks for your reply.

Actually, I'm executing them in one master procedure just to organize the
calls in one place, not for transactional consistency.

Is there any other way around this other than to call each procedure
sepatately?

All I really care about is requerying the form for successive 'completion'
records written to the table at the end of each procedure.

I can do without the times of each procedure.

Thanks.

Alan

 

[Sylvain Lafontaine]

Two possibilities:

1- You are using the same connection for calling your SP than the connection
used by Access to open the second form. Try opening a separate connection
to query your table.

2- A transaction is open when the master SP is called, all subsquent writes
are then part of this transaction and won't be visible from the outside
until the transaction is committed. Try the Read Uncommited isolation level
for reading your table (cannot be done for a bound form under Access, so you
will have to directly open a recordset from VBA). However, I don't know if
this will work in your case.

 

[Alan Z. Scharf]

Sylvaine,

Thanks for your reply.

Those sound like two good things to try.

Will let y0u know if I have any success.

Regards,

Alan

 

[Alan Z. Scharf]

Sylvaine,

Back in December you helped me with this, but I just returned to the task..

1. To recap, I'm executing a series of SP's within one master SP, and want
to display a status table on a form as each individual SP finishes.
Each individual SP writes a line to a status table when it completes.

2. In response, you replied as below at the end of this message, and I have
implemented the separate connections and READ UNCOMMITED.

3. I have now have an unbound form to display the status table, with a
Separate ADO connection than main ADP connection, with a timer event and a
me.requery ever 5 seconds. The form's recordset is based on SELECT * FROM
tblStatusWITH(READUNCOMMITTED).

4. The master SP is also called through a separate connection from the main
app connection.

5. RESULT SO FAR: The Status form properly displays, line by line as each
individual SP completes, *** ONLY IF *** I run the master SP from Query
Analyzer.

However, if I run the master SP from within Access, the Status form does not
display until the entire Master SP completes, even though the Master SP is
being executed through a separate connection.

Note: I also tried this with UPDATE instead of INSERT, with same result.

6. QUESTION: Is there any way to get this to work executing the Master SP
from within Access, or will such an effort always tie up Access because it
is a synchronous operation?

Thanks for any insight you can give.

Regards,

Alan

 

[Sylvain Lafontaine]

Did you try returning multiple recordsets (using multiple Select statements)
and using the function Recordset.NextRecordset to follow the progress?

 

[Alan Z. Scharf]

Sylvaine,

Thanks for your quick reply.

No, I didn't.

Can you give me a little more direction about what you mean? I'm not
picturing it yet. Also where to put it, in the timer event?

Thanks.

Alan

 

[Sylvain Lafontaine]

You put multiples Select statements in the stored procedure:

-- some work here --
Select 1
-- and here --
Select 2
-- and here --
Select 3

-- End of the work, send final result:
Select * from Table1 ...

 

[Alan Z. Scharf]

Sylvaine,

Is this going in the master stored procedure calling the individual stored
procedures, or is it going somewher in my ADDpapp?

Thanks.

Alan

 

[Sylvain Lafontaine]

You put this in your master procedure. Because you have multiple Select
statements, you will have multiple recordsets that will be returned. You
then use the call to .NextRecordset to access each one of them.

However, I don't know if SQL-Server will wait for the end of the procedure
to begin sending them back but it shouldn't take you to long to test for
this hypothesis. Maybe that you will have to run your command in
asynchronous mode; search Google for adAsyncExecute and take a look at the
state property of the command object, for example:

http://www.aspfree.com/c/a/Database/The-Command-Object/
http://www.aspfree.com/c/a/Database/The-Command-Object/3/

You may also have to take a look at the state property of the recordset
object.

In your case, I don't know why opening the second connection didn't work;
can you show me how did you create it?

Finally, if nothing of this work, you will have to use another protocol to
access SQL-Server (for exemple by using HTTP Endpoint or using a web service
or even running continuously a job agent on SQL-Server that will make the
call to the SP on your behalf (hence simulating the call using Enterprise
Manager).

However, using a second connection should have worked.

 

[Alan Z. Scharf]

Sylvaine,

Thanks again. I will try to get to this over the weekend.

Regards,

Alan