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.

Someone who’s pretty good programming on the front end, but not so crash hot on the backend, eg SQL Server, asked me the other day about looping through recordsets in a SQL Server Stored Procedure.

It is of course possible to use a cursor and FETCH but most SQL programmers frown on cursors in stored procedures. Cursors involve grabbing a bunch of records and locking them, there can be a substantial performance hit. They should really only be used as a last resort, for example, where you don’t have a good enough unique key value on your rows (although there are ways around that one usually as well).

The easiest way I know to look through records is to employ a temporary table, here’s a simple example:

-- Create a temporary table to hold the records to be updated
DECLARE @uniqueId int
DECLARE @TEMP TABLE (uniqueId int)
-- Insert into the temporary table a list of the records to be updated
INSERT INTO @TEMP (uniqueId)
SELECT uniqueId FROM myTable
-- Start looping through the records
WHILE EXISTS (SELECT * FROM @TEMP)
BEGIN
-- Grab the first record out
SELECT Top 1 @uniqueId = uniqueId FROM @TEMP
PRINT 'Working on @uniqueId = ' + CAST(@uniqueId as varchar(100))
-- Perform some update on the record
UPDATE myTable SET myField = 'something or other' WHERE uniqueId = @uniqueId
-- Drop the record so we can move onto the next one
DELETE FROM @TEMP WHERE uniqueId = @uniqueId
END