Re: DBD::ADO CommandTimeout

[RHope]

I've done some more investigation, and have come up with a work around for
my CommandTimeout problem.

The bottom line is I am unable to set the CommandTimeout at the database
Connection level.  In addition to what was mentioned below, I went into
ADO.pm directly trying to hard code a CommandTimeout value.  Inside the
connect() method, I've tried adding several lines, including:

  $conn->{CommandTimeout} =1000;
  $conn->LetProperty("CommandTimeout",1000);
  $conn->SetProperty('CommandTimeout',1000);
  $this->{ado_conn}->SetProperty('CommandTimeout',1000);

and from my high leve script that uses DBD::ADO I've tried:

  $dbh->{ado_conn}->SetProperty('CommandTimeout',1000);

However, if I set the command timeout on the Command object (as opposed to
the Connection object), it seems to work.  I first discovered this would
work by adding the following to ADO.pm's prepare() method:

  $comm->SetProperty('CommandTimeout',1000);

This is obviously a horrible solution, since I'd need to distribute a
modified version of ADO.pm with my application.  My slightly better
solution is to add this to my script:

  $sth = $dbh->prepare("select * from...")
  $sth->{ado_conn}->SetProperty("CommandTimeout",1000);

Is anyone else able to reproduce this? I'm using MS SQL Server 2000 sp2,
with MDAC 2.7.  I'm using DBD::ADO v2.4.  Why won't it work when I set the
CommandTimeout at the Connection level???

Obviously, my workaround will only be valid for DBD::ADO.  How likely is it
that the ADO connection object will change where it's stored internally?
Is that a fairly constant location, or will I have to re-write my script
every time I updgrade DBD::ADO?

Thanks,
Ryan Hope





                                                                                       
                                                       
                    Ryan Hope                                                          
                                                       
                                         To:     <[EMAIL PROTECTED]>                  
                                                       
                    05/20/02 05:23       cc:                                           
                                                       
                    PM                   Subject:     DBD::ADO CommandTimeout          
                                                       
                                                                                       
                                                       
                                                                                       
                                                       



I'm using DBD::ADO, and am having trouble with long SQL queries timing out.
I'm connecting to a MS SQL Server 2000 database, using the SQLOLEDB driver.
My connect string is:

dbi:ADO:Provider=SQLOLEDB;Data Source=\\servername;Initial
Catalog=databaseName;CommandTimeout=6000

Long running queries time out at about 30 seconds, no matter what I put for
the CommandTimeout value.  I've tried

CommandTimeout=1
CommandTimeout=0
CommandTimeout=60
CommandTimeout=6000000
Command Timeout=6000000
Timeout=600000

I've even gone as far as trying to modify the ADO.pm module to hard code a
longer/shorter value.

Is there any reason that the SQLOLEDB driver would be ignoring my requests
to lengthen (or shorten) the default timeout?  How can I tell what version
of the SQLOLEDB driver I have?  And are there global defaults for that
driver that I can set somewhere outside of Perl?

I've also tried modifying the server and initial database names, just to
make sure it's reading my connect string...and they are both being read
correctly.

Thanks in advance,
Ryan Hope

The error message I'm getting is:

Lasterror:      -2147217871: OLE exception from "Microsoft OLE DB Provider
for SQL Server":

Timeout expired

Win32::OLE(0.1502) error 0x80040e31
    in METHOD/PROPERTYGET "Open"
     Description:   Timeout expired
     HelpContext:   0
     HelpFile:
     NativeError:   0
     Number:        -2147217871
     Source:        Microsoft OLE DB Provider for SQL Server
     SQLState:      HYT00 at replenishNetwork.pl line 145.