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’
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.