ADO Timeouts: Connection vs. Command vs. RecordSet

The Problem

An interesting bit I learned today is that if you need to extend the time limit for an intensive SQL query or procedure executed using ADO, setting only the commandTimeout property of your ADODB.Connection instance is not sufficient. You will see that you have extended the connection time limit, but queries will still time out within the default limit of 30 seconds.

Example

Using VBScript in ASP 3:

set con = createObject("ADODB.Connection")
con.open connectionString
con.commandTimeout = 60

set command = createObject("ADODB.Command")
command.activeConnection = con
command.commandType = adCmdText
command.commandText = sql
command.execute
response.write command.commandTimeout 'Actually 30 (the default).

The Solution

You must also set the commandTimeout property on the ADODB.Command or ADODB.Recordset being used. Otherwise those objects will use the default time limit of 30 seconds because they do not inherit the time limit from the associated ADODB.Connection instance.

Example

Using VBScript in ASP 3:

set con = createObject("ADODB.Connection")
con.open connectionString
con.commandTimeout = 60

set command = createObject("ADODB.Command")
command.activeConnection = con
command.commandType = adCmdText
command.commandText = sql
command.commandTimeout = 60
command.execute
response.write command.commandTimeout 'This is now 60 seconds.

See this Microsoft Tech Note for more.