Results 1 to 4 of 4
  1. #1
    Join Date
    Jul 2003
    Location
    Frankfurt, Germany
    Posts
    20

    Question Unanswered: calling stored procedures through asynchronous commands

    hi folks,

    can somebody help me with the following problem:

    i run an front end application as access xp project on a sql server 2000 database.

    several stored procedures with different tasks are performing various operations on mass data (about 3 million records)
    but not at the same time.
    all of these procedures are entering records in a log table to notify the actual step or result.

    i can of course view this log table in the database window, while the stored procedure is still running, to see what it is doing right now or next.

    now i want to do the following:
    i have a form, where the user shall select the stored procedure to be run
    and after starting it the form shows the contents of the log table.
    a timer updates this view every 10 seconds

    BUT:
    if i run the stored procedure synchronously, of course the application would be blocked.
    so i thought, cool, let´s do it asynchronously, so i do something like:

    cmd.Execute Options:=adAsyncExecute

    i found, that long running stored procedures (talking about between 2 minutes to 24 hours !!) will need to have their timeout set because Access will stop them after the timeout (weird enough)
    so i did

    cmd.CommandTimeout = 10000
    cmd.Execute Options:=adAsyncExecute

    THE PROBLEM:
    even running the command asynchronously my form will NOT UPDATE the table view i.e. not run through the timer event.
    the code started the cmd.execute command and continued this routine, the application is not blocked, i tested that
    still: no code is being run by access while the stored procedure is still running

    somebody an idea?

    ps:
    i tried to run the command with the underlying access project connection as well as with a new adodb.connection object: same results, no difference

  2. #2
    Join Date
    Sep 2003
    Location
    UK
    Posts
    122
    it sounds like Access is waiting for the stored proc to finnish.

    It may be possible to create some middle function that triggers the stored proc and then claims to have finished thus releasing Access to beleave that it is done.
    Matt the Hat says: "what!?"
    A child of five could understand this! Fetch me a child of five!
    SCARY HARD CHALLENGE: http://www.dbforums.com/showthread.php?threadid=988682

  3. #3
    Join Date
    Jul 2003
    Location
    Frankfurt, Germany
    Posts
    20
    Originally posted by Matt_T_hat
    it sounds like Access is waiting for the stored proc to finnish.

    It may be possible to create some middle function that triggers the stored proc and then claims to have finished thus releasing Access to beleave that it is done.
    well, this middle procedure in my application is already implemented in my opinion as a class library, that does only data operation stuff on the sql server.

    and, i mean: "asynchronous" is supposed to MEAN "asynchronous": Access is not supposed to wait for the procedure to finish when i call it asynchronously, right?

    if this does not work properly, what is one supposed to do? write a vb or c++ dcomm dll to run f... stored procedures outside of access, so it will NOT block the access application???

    grrrrr....

    anybody any ideas, still????

    thanx
    ´
    cr

    keep rockin

  4. #4
    Join Date
    Oct 2003
    Location
    Sweden
    Posts
    45
    Is it possible that the stored procedures produce locks on the tables preventing reads from the table?
    Can you read from any other table or DB while the asynchronous command is being executed?

    If the SPs are doing major updates within a transaction as is usual it might produce big locks preventing you to scan the table (unless you allow dirty reads or something).

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •