Re: Indicating Stored Procedure progress on Form

Sylvaine,

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

Regards,

Alan

"Sylvain Lafontaine" <sylvain aei ca (fill the blanks, no spam please)>
wrote in message news:eMYNCLbXHHA.4628@xxxxxxxxxxxxxxxxxxxxxxx
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.

--
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:OWG5S3XXHHA.596@xxxxxxxxxxxxxxxxxxxxxxx
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" <sylvain aei ca (fill the blanks, no spam please)>
wrote in message news:etxIiqJXHHA.2052@xxxxxxxxxxxxxxxxxxxxxxx
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: OLEDB Connection to SQL Server Compact
    ... A connection object must be used with a single recordset ... Sylvain Lafontaine, ing. ... 1)First the "conn" object opens giving no errors in the error collection.. ...
    (microsoft.public.access.modulesdaovba)
  • Re: ODBC Connection to SQL Server Compact
    ... Possibly because you are trying to use two open recordset on the same ... A connection object must be used with a single recordset ... Sylvain Lafontaine, ing. ... 1)First the "conn" object opens giving no errors in the error collection.. ...
    (microsoft.public.access.modulesdaovba)
  • Re: Indicating Stored Procedure progress on Form
    ... You put this in your master procedure. ... You may also have to take a look at the state property of the recordset ... using a second connection should have worked. ... Sylvain Lafontaine, ing. ...
    (microsoft.public.access.adp.sqlserver)
  • 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
    ... Sylvain Lafontaine, ing. ... 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. ...
    (microsoft.public.access.adp.sqlserver)
Loading