CommandTimeout - no effect? 2

[Transcend]

Hi everyone

I'm a bit confused about the timeout on an ADO command object. I have some code that is opening a recordset. I'm setting the command object timeout to 15 seconds, and using a query that I know takes at least 4 minutes to run in query analyser. The thing is when i run the code it does not time out. Is my understanding of how this should work wrong? The code is inherited i didn't write it, but i can't see why the timeout is not occurring. In this case i have set lTimeout to 15.

This is the code:

Call Connect

If Not m_objConn Is Nothing Then
If m_objConn.State = adStateOpen Then
'// If transactional then start a transaction
If blnTransactional Then
Call m_objConn.BeginTrans
End If

'// Allow the command to take as long as required
objCommand.CommandTimeout = lTimeOut

'// Open the resultset using the command object
Set objCommand.ActiveConnection = m_objConn
Set objRS = New ADODB.Recordset
With objRS
.CursorLocation = adUseClient
.CursorType = adOpenStatic
.LockType = adLockReadOnly
Set .ActiveConnection = m_objConn

Call .Open(objCommand)
End With

If (objRS.State = adStateClosed) Then
Set objRS = Nothing

' Call Err.Raise(NULLRECORDSET, "", "Connnection to " & objCommand.Name & " has Failed. " & _
' "Stored Procedure may not exist, or interface may be incompatible with " & _
' "the current version of IMS")
End If

Set ExecuteResultset = objRS

'// If transactional then commit the transaction
If blnTransactional Then
Call m_objConn.CommitTrans
End If

End If
End If


This line here is the one i expect to timeout

Call .Open(objCommand)

but it doesn't!

Any ideas?

Transcend

 

[chiph]

Set your timeout after you set the ActiveConnection.

There's a bug in the ADO documentation -- it leads you to believe that if you set the timeout on the Connection object, it will be inherited by your Command objects that use that connection. This is not true. You must set the CommandTimeout every time, after you set the ActiveConnection (the timeout gets cleared after setting the connection).

Chip H.


If you want to get the best response to a question, please check out FAQ222-2244 first

 

[Transcend]

Hi chip and thanks for your response.

I moved this line: objCommand.CommandTimeout = lTimeOut
to after the one where the active connection is set. lTimeOut has a value of 15 seconds but it still doesn't time out!! It just runs and takes about four minutes. How can i force this to time out?

Transcend

 

[Transcend]

This is driving me mad!!! Anyone out there have a clue as to why it wouldn't be timing out?

Even when i change the timeout to 60, or 120 seconds (thinking maybe 15 seconds would just be ignored) it still doesn't time out!

Transcend

 

[ArtieChoke]

When you say the query runs for 4 minutes, does it return data during the whole time it runs, or does it take 4 minutes before any data appears?

According to an ADO reference book I have, the command timeout is only applicable to the time period when the server accepts the command and when the first record is returned. If your query is returning rows all of this time, then the command timeout won't work.

I'm not clear on why you would want the program to time out on a query in 15 seconds if you know it takes 4 minutes to run...

"I think we're all Bozos on this bus!" - Firesign Theatre

 

[Transcend]

I'm actually trying to figure out why a call to a stored procedue was not timing out at all and defaulting to the system timeout. It's part of a much bigger and complex problem, but the part where i couldn't produce a timeout has had me stumped!

Transcend

 

[Transcend]

Well i guess i can't really do what i was trying to do. Microsoft suggests running the query asynchronously, and using a timer, as ado does not have a querytimeout property exposed.

Thanks again

Transcend