?
Solved

Setting ADODB CommandTimeout

Posted on 2009-04-22
3
Medium Priority
?
1,337 Views
Last Modified: 2013-12-25
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

0
Comment
Question by:dnorvell
  • 2
3 Comments
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 24208559
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
 

Author Comment

by:dnorvell
ID: 24208645
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
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 2000 total points
ID: 24208735
>I'm assuming that the CommandTimeout property stays in effect until reset or the connection is closed.
yes


0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Over the years I have built up my own little library of code snippets that I refer to when programming or writing a script.  Many of these have come from the web or adaptations from snippets I find on the Web.  Periodically I add to them when I come…
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
Suggested Courses

840 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question