Does timed-out SP finish processing or terminate?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

feaganss - 15 May 2008 19:54 GMT

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

Reply to this Message

Tom van Stiphout - 16 May 2008 05:45 GMT

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.

>Hello,
>
[quoted text clipped - 13 lines]
>
>Shawn

Reply to this Message

Sylvain Lafontaine - 16 May 2008 05:59 GMT

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=deadlock+sql&rls=com.microsoft:en-ca:IE-SearchBox
&ie=UTF-8&oe=UTF-8&sourceid=ie7&rlz=1I7GGLD

Signature

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

> Hello,
>
[quoted text clipped - 19 lines]
>
> Shawn

Reply to this Message

feaganss - 16 May 2008 12:35 GMT

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:
[quoted text clipped - 6 lines]
>>
>> Shawn

Reply to this Message