Setting CommandTimeout

amitvalia has asked for the wisdom of the Perl Monks concerning the following question:

Hi All.

I'm trying to set the connectionTimeout and CommandTimeout properties in ASP using PerlScript. I'm doing this because some of my queries are coming back with

Query Timeout Expired error.

Hence I try to set the commandTimeout parameter to 120. That's when I get another error.

PerlScript Error error '80004005' (in cleanup) Can't modify non-lvalue subroutine call

My code is

$Conn = $Server->CreateObject("ADODB.Connection");
$Conn->Open( "Provider=SQLOLEDB;User ID=USER;Password=PASSWORD;Initial
+ Catalog=DB;Data Source=SERVER" );
$Conn->CommandTimeout = 120;
$timeout = $Conn->CommandTimeout;

$sql = "Select Col1,Col2 from Table1 ";
$RS = $Conn->Execute( $sql,CommandTimeout=120);
if($Conn->Errors->{Count} > 0) {
%> Error is <% =$Conn->Errors(0)%>


...
[download]

Comment on Setting CommandTimeout Download Code

Replies are listed 'Best First'.
Re: Setting CommandTimeout by NetWallah (Canon) on May 05, 2016 at 20:57 UTC
In "$Conn->CommandTimeout = 120;" you are attempting to set the value of a subroutine (lvalue). That is the way the command is written in VB(A). For perl, try: `$Conn->CommandTimeout ( 120 );` [download] I dont think this syntax is right either: `$RS = $Conn->Execute( $sql,CommandTimeout=120);` [download] but I'm unfamiliar with this approach for SQL/Win32::OLE. This is not an optical illusion, it just looks like one.	[reply] [d/l] [select]
Re^2: Setting CommandTimeout by amitvalia (Novice) on May 05, 2016 at 22:37 UTC
When you run `$RS = $Conn->Execute( $sql,CommandTimeout=120);` you get an error Can't modify constant item in scalar assignment But the line `$Conn->CommandTimeout ( 120 );` Worked when compiling. But still did not change the CommandTimeout value. `<%@ LANGUAGE = PerlScript%> <html> <body> <% $Conn = $Server->CreateObject("ADODB.Connection"); $Conn->Open( "Provider=SQLOLEDB;User ID=user;Password=pswd;Initial Cat +alog=db;Data Source=server" ); $Conn->CommandTimeout(120); %> Command Timeout is now =<% =$Conn->CommandTimeout%> <% $RS = $Conn->Execute( "SELECT 19000000+max(yymmdd) FROM tehachapi. +.bso688d" ); $yymmdd=$RS->Fields(0)->{Value}; $RS->Close; $Conn->Close; %>` [download] This is what the is printed Command Timeout is now =30	[reply] [d/l] [select]
Re^3: Setting CommandTimeout by amitvalia (Novice) on May 05, 2016 at 22:40 UTC
I meant to say `$Conn->CommandTimeout(120)` Didnt give a compile error. But it didn't set the commandtimeout value either.	[reply] [d/l]
Re^4: Setting CommandTimeout by NetWallah (Canon) on May 06, 2016 at 00:47 UTC
Re: Setting CommandTimeout by thezip (Vicar) on May 05, 2016 at 20:57 UTC
Howdy amitvalia, I'm sure you've already tried this, but have you tried executing your query in a SQL Server Query Analyzer window? Perhaps you can optimize the query so that it doesn't need to time out? My tenacity goes to eleven...	[reply]
Re^2: Setting CommandTimeout by amitvalia (Novice) on May 05, 2016 at 21:57 UTC
I did. I did get the time down a bit. But based on the network speed at the time, the query comes back in a few seconds or does not and times out.	[reply]