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).
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.
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.
Posts
Booo!!! I am an 'ignant biologist and don't understand your blog at all...
ReplyDeleteVery cool. You saved my day.
ReplyDelete"You must also set the commandTimeout property on the ADODB.Command or ADODB.Recordset being used."
ReplyDeleteADODB.Recordset doesn't have a commandTimeout property:
http://msdn.microsoft.com/en-us/library/windows/desktop/ms675841(v=vs.85).aspx
For Recordset we should to write connection.commandTimeout = 60 also, but before connection.open only
DeleteAnonymous, the solution worked at the time, but do you have a better solution that you'd like to share?
ReplyDeleteThank you so much! After 6 hours of strugling - You saved my day!
ReplyDeleteIs there a way to retrieve rows asynchronously? A way where I can catch those rows during the return of a Select command?
ReplyDeleteThere's a Select that is taking too much time to return - almost 90 secs - during the opening of my program.
All this time make the user uncomfortable with the delay.
Thanks.