Setting ADODB CommandTimeout

I found a post by Miron regarding Error  -2147012894, where he provided code for setting the CommandTimeout property.  The code from his post is included in the code section below.  A couple follow-up questions:
1. Should the typo in the last line 'com.Execute' be  'cmd.Execute' or 'con.Execute'?
2. Does setting the CommandTimeout property hold for as long as the connection is open or does it need to be set for each con.Execute?

I'm trying to apply the post to our application, but need a little more help. We typically open the connection upfront, without creating an ADODB command object:

Dim MyConn
Set MyConn = Server.CreateObject("ADODB.Connection")
MyConn.open "Provider=SQLOLEDB;Data Source=my_server;Initial Catalog=my_database;UID=my_user;PWD=my_password;"

and then uses it repeatedly with calls like:
Set RS = MyConn.Execute(mySQLstr)

until the program exits.  How would I set the CommandTimeout for the program?


set con = CreateObject( "ADODB.Connection" )
set cmd = CreateObject( "ADODB.Command" )
 
con.open "Provider=SQLOLEDB;Data Source=my_server;Initial Catalog=my_database;UID=my_user;PWD=my_password;"
 
 
set cmd.ActiveConnection = con
 
' set timeout to long enought value
cmd.CommandTimeout = 3600  '( 1 hour, seconds )
cmd.CommandText = "select ... | execute my_procedure ..."
 
set rs = com.Execute

Open in new window

dnorvellAsked:
Who is Participating?
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>I'm assuming that the CommandTimeout property stays in effect until reset or the connection is closed.
yes


0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
the cconnection object has a command timeout property as well
Dim MyConn
Set MyConn = Server.CreateObject("ADODB.Connection")
MyConn.open "Provider=SQLOLEDB;Data Source=my_server;Initial Catalog=my_database;UID=my_user;PWD=my_password;"
MyConn.CommandTimeout = 36000
Set RS = MyConn.Execute(mySQLstr)

Open in new window

0
 
dnorvellAuthor Commented:
Thanks,  
I'm assuming that the CommandTimeout property stays in effect until reset or the connection is closed.  Also, MSDN talks about CommandTimeout property on both the connection and command objects and that each is independant of the other.  Wasn't sure if the command (MyConn.CommandTimeout = 36000) would have the desired effect for the MyConn.Execute.  I'll give it a try.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.