Does timed-out SP finish processing or terminate?

[feaganss]

Hello,

I'm using Access 2003 with a SQL 2000 back-end. I have an OnClose event in a
form that calls a function, and the function calls a Stored Procedure in SQL.
There have been occasional strange results where it looks like only the first
portion of the SP executes, and I have not been able to find the cause. I'm
wondering, though, whether a timeout could be the problem. Does anyone know
if a SP stops executing when/if there's an ADO timeout in Access, or if it
completes after the timeout and just doesn't communicate that back to ADO?

Now that I'm reasonably sure I know which function is causing the problem,
I'm increasing the timeout so the problem may just go away, but I'd sure feel
better if I knew for sure whether this is how it works or not.

Any help would be appreciated....

Shawn

 

[Tom van Stiphout]

Typically when you .Execute a stored procedure, it's a
shoot-and-forget proposition. I'm not even sure there is a way - if
you even wanted to - that Access could say in the middle of a lengthy
sproc: that's enough, cancel right now.
So the timeout you're experiencing is more likely a SQL Server
timeout. At least that's where I would be looking first.
I would also concentrate my efforts on making the sproc execute much
more quickly. Sometimes another pair of eyes, a bit of thinking
out-of-the-box can make orders of magnitude of difference.

-Tom.

 

[Sylvain Lafontaine]

Unless what the SP is doing is very lengthy or the server is really
overloaded, what you are describing could be a dead-locking problem:

http://www.google.com/search?q=dead...ox&ie=UTF-8&oe=UTF-8&sourceid=ie7&rlz=1I7GGLD

 

[feaganss via AccessMonster.com]

Thank you to both of the responders, this is helpful. The SP typically takes
< 5 seconds to execute, so the more likely scenario seems to be the deadlock
problem you mentioned. It works correctly about 95% of the time, but the
other 5% seems to only partially execute, which is what led me to the timeout
idea thinking that maybe the server just happened to be bogged when those 5%
were executed. At any rate, I'm going to chase down the deadlock idea and
see what I can find....

Thanks again!

Shawn

Unless what the SP is doing is very lengthy or the server is really
overloaded, what you are describing could be a dead-locking problem:

http://www.google.com/search?q=dead...ox&ie=UTF-8&oe=UTF-8&sourceid=ie7&rlz=1I7GGLD

Hello,

[quoted text clipped - 19 lines]

Shawn