INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Timeout error when useing .commandTimeout = 0???

Timeout error when useing .commandTimeout = 0???

(OP)
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
 

RE: Timeout error when useing .commandTimeout = 0???

(OP)
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...

RE: Timeout error when useing .commandTimeout = 0???


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

CODE


    PublicCommSP.CommandType = adCmdStoredProc
    PublicCommSP.CommandText = SPName
    PublicConn.CursorLocation = adUseClient
    PublicConn.CommandTimeout = 0
  
    PublicConn.open()


    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

RE: Timeout error when useing .commandTimeout = 0???

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 jester

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close