Re: Indicating Stored Procedure progress on Form

Tech-Archive recommends: Fix windows errors by optimizing your registry

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 ...

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)


"Alan Z. Scharf" <ascharf@xxxxxxxxxxxxxx> wrote in message
news:uFE0JUFXHHA.1036@xxxxxxxxxxxxxxxxxxxxxxx
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" <sylvain aei ca (fill the blanks, no spam please)>
wrote in message news:O6VCJaEXHHA.4964@xxxxxxxxxxxxxxxxxxxxxxx
Did you try returning multiple recordsets (using multiple Select
statements) and using the function Recordset.NextRecordset to follow the
progress?

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)


"Alan Z. Scharf" <ascharf@xxxxxxxxxxxxxx> wrote in message
news:eTw$DBEXHHA.4252@xxxxxxxxxxxxxxxxxxxxxxx
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" <sylvain aei ca (fill the blanks, no spam please)>
wrote in message news:eGWYiM6IHHA.3872@xxxxxxxxxxxxxxxxxxxxxxx
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.








.

Relevant Pages

  • Re: Indicating Stored Procedure progress on Form
    ... To recap, I'm executing a series of SP's within one master SP, and want ... Separate ADO connection than main ADP connection, with a timer event and a ... The form's recordset is based on SELECT * FROM ... SP is being executed through a separate connection. ...
    (microsoft.public.access.adp.sqlserver)
  • Re: Indicating Stored Procedure progress on Form
    ... Separate ADO connection than main ADP connection, ... The master SP is also called through a separate connection from the ... SP is being executed through a separate connection. ... writes are then part of this transaction and won't be visible from the ...
    (microsoft.public.access.adp.sqlserver)
  • Re: Indicating Stored Procedure progress on Form
    ... Is this going in the master stored procedure calling the individual stored ... Sylvain Lafontaine, ing. ... Separate ADO connection than main ADP connection, ... Master SP is being executed through a separate connection. ...
    (microsoft.public.access.adp.sqlserver)
  • Re: Indicating Stored Procedure progress on Form
    ... Separate ADO connection than main ADP connection, with a timer event and a ... The master SP is also called through a separate connection from the main ... Is there any way to get this to work executing the Master SP ...
    (microsoft.public.access.adp.sqlserver)
  • Re: Big knowledge gap wrt TCP/IP
    ... forward whatever I recieve - the connection ... >From the POV of the power-line communication mechanism, ... We have a master machine in the background, ... Now, although slave1 needs to talk to slave2, there is no way to ...
    (comp.lang.tcl)