Timeout error when useing .commandTimeout = 0???

[wendas]

I was hoping someone could look at this code and let me know if we placed the .commandTimeout in the wrong place, or don't have it connected right.

I was told using .commandTimeout = 0 meant there was no timeout.. Yet we still get them.

Unfortunately the DB are huge as users want to keep everything FOREVER.. So we can streamline only so far.
______________________________________________

Public PublicCommSP As New ADODB.Command
Public rsPublicSP As New ADODB.Recordset
Public PublicConn as new ADODB.Connection


Public Function PublicSPFind(SPName As String) as integer

PublicCommSP.CommandType = adCmdStoredProc
PublicCommSP.CommandText = SPName
PublicConn.CursorLocation = adUseClient
'This help get rid of the command Timeout error. Setting to 0 means wait indefinately
PublicConn.CommandTimeout = 0
PublicCommSP.ActiveConnection = PublicConn

'Open SP Recordset
Set rsPublicSP = New ADODB.Recordset
rsPublicSP.CursorType = adOpenDynamic
rsPublicSP.LockType = adLockOptimistic
Set rsPublicSP.Source = PublicCommSP
rsPublicSP.Open

 

[wendas]

I may have figured it out. But would love confirmation.

I found an example where when they open the connection they use PublicConn.ConnectionTimeout.

We are doing that earlier in the code, I checked.

But here I should NOT be pointing to the connection, I should be pointing to the command for the Command Timeout.

So it should be useing

PublicCommSP.CommandTimeout = 0

not
PublicConn.CommandTimeout = 0

I did this and it worked. Problem is this timeout is finicky based on the load on the server so sometimes it times out, other times it does not. Therefore I know this change did not hurt it, but not sure if it helped it.

Can someone confirm if I am accurate.. Thanks...

 

[MarkSweetland]

Try opening the connection object prior to setting the ActiveConnection in the command object:

    PublicCommSP.CommandType = adCmdStoredProc
    PublicCommSP.CommandText = SPName
    PublicConn.CursorLocation = adUseClient
    PublicConn.CommandTimeout = 0 
[red]  
    PublicConn.open()[/red]

    PublicCommSP.ActiveConnection = PublicConn

'Open SP Recordset
    Set rsPublicSP = New ADODB.Recordset
    .
    .
    .

Mark

"You guys pair up in groups of three, then line up in a circle."
- Bill Peterson, a Florida State football coach

 

[ArtieChoke]

Those timeouts are within ADO only. They do not have any effect on the database timeouts. They tell ADO how long to wait - so a zero command timeout will have ADO wait forever for the command to finish, but you can still get a timeout from the server.

Make sure your query is using indexes or primary keys so the query executes fast. Or don't return so much data. Some complex queries that run fine in the database query tool take forever in ADO.

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