I was working on an ASP page today that included an AJAX call off to trigger various SQL updates, amongst which was a call to a stored procedure. The page started to error with:
Microsoft OLE DB Provider for SQL Server error ‘80040e31’
Timeout expired
The error line was the call to the stored procedure. After a bit of fiddling around I traced the issue to the ADO ConnectionTimeOut value. By default this is 30 seconds.
There are, I guess, 3 timeout values of interest on an ASP page:
1. Session timeout
How long the user’s browser session will last. On IIS this is defaulted to 20 minutes
2. Script timeout
How long a page can take to complete. On IIS this is defaulted to 90 seconds.
3. Connection timeout
How long a single call to the database can take to complete. This defaults to 30 seconds.
The stored procedure was taking > 30 seconds, hence throwing an error. I solved the problem by increasing the ConnectionTimeOut value for the particular call to the stored procedure, eg:
Dim cmdC Set cmdC = Server.CreateObject("ADODB.Command") with cmdC .ActiveConnection = conn .CommandText = "[procedure name]" .CommandType =adCmdStoredProc .CommandTimeout = 3000 .Execute end with
The value is in seconds, so 3000 seconds = 5 minutes. You can read more about ConnectionTimeOut here.
Of course, there’s a whole other discussion why we have a stored procedure taking so long to run as part of an AJAX call. We’ll probably shift it off to a scheduled job or something eventually but we needed a quick fix for the interim.
Morning,
Sorry to be picky, but I think 3000 seconds is not the same as 5 minutes! It’s actually 50 minutes. However, I expect the effect would be the same!
Cheers
LordScree
🙂
Must be why I failed math at school. Couldn’t count.
LordScree you are right. But as you point out the effect is the same.
David
Pingback: I need to write more blog posts about SQL server and missing toolbars in Excel | David Eedle
Where did you write that code that increases the ConnectionTimeOut value for the particular call to the stored procedure ?
Hi Nathan. Not completely sure I understand your question. In the ASP code I have a call to a stored procedure, like the one in my blog post. This line: .CommandTimeout = 3000.
Hi…Got struck with SQL Server error ‘80040e31′ and the solution worked fine… I just set .CommandTimeout = 0