Results 1 to 12 of 12

Thread: Do you use ExecuteComplete Event (ADO)

  1. #1

    Thread Starter
    MS SQL Powerposter szlamany's Avatar
    Join Date
    Mar 2004
    Location
    Connecticut
    Posts
    18,263

    Do you use ExecuteComplete Event (ADO)

    Just wondering how many of you out there do async ADO calls and use the ExecuteComplete Event of the connection to determine they are complete.

    *** Read the sticky in the DB forum about how to get your question answered quickly!! ***

    Please remember to rate posts! Rate any post you find helpful - even in old threads! Use the link to the left - "Rate this Post".

    Some Informative Links:
    [ SQL Rules to Live By ] [ Reserved SQL keywords ] [ When to use INDEX HINTS! ] [ Passing Multi-item Parameters to STORED PROCEDURES ]
    [ Solution to non-domain Windows Authentication ] [ Crazy things we do to shrink log files ] [ SQL 2005 Features ] [ Loading Pictures from DB ]

    MS MVP 2006, 2007, 2008

  2. #2

    Thread Starter
    MS SQL Powerposter szlamany's Avatar
    Join Date
    Mar 2004
    Location
    Connecticut
    Posts
    18,263

    Re: Do you use ExecuteComplete Event (ADO)

    It's been 3 hours and 4 VIEWS of this post...

    ...no replies yet...

    Does anyone have any experience with ASYNC ADO use?

    *** Read the sticky in the DB forum about how to get your question answered quickly!! ***

    Please remember to rate posts! Rate any post you find helpful - even in old threads! Use the link to the left - "Rate this Post".

    Some Informative Links:
    [ SQL Rules to Live By ] [ Reserved SQL keywords ] [ When to use INDEX HINTS! ] [ Passing Multi-item Parameters to STORED PROCEDURES ]
    [ Solution to non-domain Windows Authentication ] [ Crazy things we do to shrink log files ] [ SQL 2005 Features ] [ Loading Pictures from DB ]

    MS MVP 2006, 2007, 2008

  3. #3
    Member appdalesolution's Avatar
    Join Date
    Nov 2004
    Location
    London
    Posts
    37

    Re: Do you use ExecuteComplete Event (ADO)

    personally i write most ADO apps disconnected so dont use any async operations was there anything you wanted to know in particular?
    Lee Dale
    http://www.appdalesolutions.co.uk
    Web Design, Database/Software Developement, Hardware Upgrades, Network design/maintenance.

  4. #4
    Fanatic Member Blade's Avatar
    Join Date
    Jan 1999
    Location
    Stoke-on-Trent, UK
    Posts
    527

    Re: Do you use ExecuteComplete Event (ADO)

    I do the same appdalesolution. Just work with disconnected recordsets all the time. Fire off the proc call and wait for it to come back.

  5. #5

    Thread Starter
    MS SQL Powerposter szlamany's Avatar
    Join Date
    Mar 2004
    Location
    Connecticut
    Posts
    18,263

    Re: Do you use ExecuteComplete Event (ADO)

    I was curious if any used the ExecuteComplete Event...

    Let's say I've got a SPROC that take 5 minutes to complete (these are very rare - but let's say that's the case).

    It spends most of it's time processing on the server and will eventually spit back a recordset.

    Seems ASYNC and ExecuteComplete were designed for this - allow the client code to continue (no NOT RESPONDING white screen)...

    How do you use DISCONNECTED RECORDSETS in a fashion that allows you to fire them off and you aren't waiting on the VB side for completion?

    *** Read the sticky in the DB forum about how to get your question answered quickly!! ***

    Please remember to rate posts! Rate any post you find helpful - even in old threads! Use the link to the left - "Rate this Post".

    Some Informative Links:
    [ SQL Rules to Live By ] [ Reserved SQL keywords ] [ When to use INDEX HINTS! ] [ Passing Multi-item Parameters to STORED PROCEDURES ]
    [ Solution to non-domain Windows Authentication ] [ Crazy things we do to shrink log files ] [ SQL 2005 Features ] [ Loading Pictures from DB ]

    MS MVP 2006, 2007, 2008

  6. #6
    Fanatic Member Blade's Avatar
    Join Date
    Jan 1999
    Location
    Stoke-on-Trent, UK
    Posts
    527

    Re: Do you use ExecuteComplete Event (ADO)

    We never have procs that run anywhere near 5 minutes on the live system so has never been a problem to me. In fact, we have traces running all the time and anything over a few seconds is considered slow and will be checked out. For the record, this is an online company processing approx 400-500 transactions a sec on average 24hrs per day so the db is not small!! Largest table is about 65 million rows.

    Can only imagine that proc calls that take 5 mins to run must be for some sort of real-time detailed reporting purposes. We have a cube built for that, but it runs overnight on a backup + the odd report thats run on log-shipped replicated databases.

    In the apps, I simply change the mousepointer to a hourglass on every proc call so if any do take a little longer that normal, there is a visible sign that something is happening.

    I know it doesn't help you in the slightest, but just wanted to give you an insight into how we do things here. I always find it fascinating to see how other people do stuff!

  7. #7

    Thread Starter
    MS SQL Powerposter szlamany's Avatar
    Join Date
    Mar 2004
    Location
    Connecticut
    Posts
    18,263

    Re: Do you use ExecuteComplete Event (ADO)

    The couple of SPROCS that take more than a few seconds are doing things like adjudicating medical claims, preparing eligibility downloads for transmission to prescription houses and stuff like scheduling students into classes.

    We also do the hour glass and tell the very few users who run these options to be patient. But every now and then they are out and someone is filling in for them and starts freaking out - it doesn't take much mouse clicking and window switching to get a not-responding message on a PC. It always comes back when done - so it's not a real problem - just hard to explain to a user.

    So if a ASYNC and ExecuteComplete change to the client program would work - it would be easy to implement. Otherwise we will eventually off load these few processes to SERVER side scheduled tasks and eliminate the user all together.

    *** Read the sticky in the DB forum about how to get your question answered quickly!! ***

    Please remember to rate posts! Rate any post you find helpful - even in old threads! Use the link to the left - "Rate this Post".

    Some Informative Links:
    [ SQL Rules to Live By ] [ Reserved SQL keywords ] [ When to use INDEX HINTS! ] [ Passing Multi-item Parameters to STORED PROCEDURES ]
    [ Solution to non-domain Windows Authentication ] [ Crazy things we do to shrink log files ] [ SQL 2005 Features ] [ Loading Pictures from DB ]

    MS MVP 2006, 2007, 2008

  8. #8
    Member appdalesolution's Avatar
    Join Date
    Nov 2004
    Location
    London
    Posts
    37

    Re: Do you use ExecuteComplete Event (ADO)

    the problem with async is that it doesnt work in distributed or disconnected environments. Its has to be a client/server system.

    While i have used ADO events like this i cant say i use them regulary. If you have an always connected system then its fine to use these events. Otherwise try looking into com+ events and MSMQ more
    Lee Dale
    http://www.appdalesolutions.co.uk
    Web Design, Database/Software Developement, Hardware Upgrades, Network design/maintenance.

  9. #9

    Thread Starter
    MS SQL Powerposter szlamany's Avatar
    Join Date
    Mar 2004
    Location
    Connecticut
    Posts
    18,263

    Re: Do you use ExecuteComplete Event (ADO)

    Quote Originally Posted by appdalesolution
    the problem with async is that it doesnt work in distributed or disconnected environments. Its has to be a client/server system.

    While i have used ADO events like this i cant say i use them regulary. If you have an always connected system then its fine to use these events. Otherwise try looking into com+ events and MSMQ more
    We are in a tight client/server connected recordset (although we load the RS and drop it immediately!) system...

    I might experiment with this ExecuteComplete Event and see what trouble I can get myself into!

    *** Read the sticky in the DB forum about how to get your question answered quickly!! ***

    Please remember to rate posts! Rate any post you find helpful - even in old threads! Use the link to the left - "Rate this Post".

    Some Informative Links:
    [ SQL Rules to Live By ] [ Reserved SQL keywords ] [ When to use INDEX HINTS! ] [ Passing Multi-item Parameters to STORED PROCEDURES ]
    [ Solution to non-domain Windows Authentication ] [ Crazy things we do to shrink log files ] [ SQL 2005 Features ] [ Loading Pictures from DB ]

    MS MVP 2006, 2007, 2008

  10. #10
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    41,929

    Re: Do you use ExecuteComplete Event (ADO)

    A few years back we used a similar (but not as good) function of DAO as there were a few queries that took 30+ seconds.

    There weren't any suitable exposed events, so we just ran async queries and had a "Do While .StillExecuting" loop (with DoEvents + Sleep).

    There weren't any "not responding" style messages after we implemented it, but it did slow things down slightly - I think this event will do the job nicely, if it works in your situation.

  11. #11

    Thread Starter
    MS SQL Powerposter szlamany's Avatar
    Join Date
    Mar 2004
    Location
    Connecticut
    Posts
    18,263

    Re: Do you use ExecuteComplete Event (ADO)

    Quote Originally Posted by si_the_geek
    A few years back we used a similar (but not as good) function of DAO as there were a few queries that took 30+ seconds.

    There weren't any suitable exposed events, so we just ran async queries and had a "Do While .StillExecuting" loop (with DoEvents + Sleep).

    There weren't any "not responding" style messages after we implemented it, but it did slow things down slightly - I think this event will do the job nicely, if it works in your situation.
    Thanks for the info...

    I would really like to hear from someone who actually uses this EVENT - but apparently they aren't around...

    What do you suggest the VB client does while it's waiting for the event to fire? Put up a MODAL box saying "CANCEL" if you want? Certainly don't want the user continuing on like they are done and ready to move onto bigger and better things.

    *** Read the sticky in the DB forum about how to get your question answered quickly!! ***

    Please remember to rate posts! Rate any post you find helpful - even in old threads! Use the link to the left - "Rate this Post".

    Some Informative Links:
    [ SQL Rules to Live By ] [ Reserved SQL keywords ] [ When to use INDEX HINTS! ] [ Passing Multi-item Parameters to STORED PROCEDURES ]
    [ Solution to non-domain Windows Authentication ] [ Crazy things we do to shrink log files ] [ SQL 2005 Features ] [ Loading Pictures from DB ]

    MS MVP 2006, 2007, 2008

  12. #12
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    41,929

    Re: Do you use ExecuteComplete Event (ADO)

    That's a good idea, as long as the cancel works (I have seen DAO wait until the data arrives before the cancel fires!). A couple of times I have used a form like that, with an appropriate AVI file on it too - just to give the user something to watch


    Whether or not you do it, definitely disable the form(s) where they could work with data.

Posting Permissions

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



Click Here to Expand Forum to Full Width