Using ado/ole-db, I create a connection to a database (SQL 2000) specifying
a time-out value of 43200 (which should equate to 12 hours).
I then create a command and use the active connection and also take the
added precaution (which is probably pointless) of setting the command
timeout value to also be 12 hours.
I then execute some SQL that has been dynamically prepared (and could take
many hours to complete) and run this. However, I get an error in the
Application event log showing that the connection timed-out. This doesn't
happen everytime - most times it connects and runs flawlessly... The error
details for the timeout are:
Err.Number = -214746759
Err.Description = Timeout expired
And this occurs in the region of approximately 1 minute after the code was
initialised (the machine is ultra busy at this time).
I may well be wrong here, but I assume that it wasn't the connection that
timed out (i.e. was made and then was dropped) but that there's a specified
time allowed to make the connection and that it's this allowed time that is
too short - remember, the machine is ultra busy at this time). If I am
right in this assumption then I need a way of telling it not to give up
quite so quickly (or to retry several hundred times). Any insight as to
what might be happening and any suggestions concerning how to fix this would
be most welcome.
I include my code below (error handling and app logging removed for
clarity):
' Declarations
Dim oCmd As ADODB.Command
Dim oCn As ADODB.Connection
Dim lTimeOut As Long
'--
' Set the timeout ( = 43200 seconds = 12 hours)
lTimeOut = CLng(mIni.iniKeyValue(mIni.computerName, "timeOut"))
'--
' Create a new connection to the database
Set oCn = New ADODB.Connection
With oCn
.ConnectionString = mIni.iniKeyValue(computerName,
"connectionString")
.CommandTimeout = lTimeOut
.Open
End With
'--
' Instantiate the command object
Set oCmd = New ADODB.Command
With oCmd
'--
Set .ActiveConnection = oCn
.CommandType = adCmdText
.CommandText = sSQL
.CommandTimeout = lTimeOut
'--
' Execute the SQL
.Execute
'--
' Disconnect command
Set .ActiveConnection = Nothing
'--
End With
'--
' Trash the command and connection objects
oCn.Close
Set oCn = Nothing
Set oCmd = Nothing
'--
Many thanks in advance
GriffHi GriffithsJ, try using CommandTimeout = 0.
Vctor Koch From Argentina.
"GriffithsJ" <GriffithsJ_520@.hotmail.com> escribi en el mensaje
news:uzXSsXp2DHA.2060@.TK2MSFTNGP10.phx.gbl...
quote:
> Hi
> Using ado/ole-db, I create a connection to a database (SQL 2000)
specifying
quote:
> a time-out value of 43200 (which should equate to 12 hours).
> I then create a command and use the active connection and also take the
> added precaution (which is probably pointless) of setting the command
> timeout value to also be 12 hours.
> I then execute some SQL that has been dynamically prepared (and could take
> many hours to complete) and run this. However, I get an error in the
> Application event log showing that the connection timed-out. This doesn't
> happen everytime - most times it connects and runs flawlessly... The
error
quote:
> details for the timeout are:
> Err.Number = -214746759
> Err.Description = Timeout expired
> And this occurs in the region of approximately 1 minute after the code was
> initialised (the machine is ultra busy at this time).
> I may well be wrong here, but I assume that it wasn't the connection that
> timed out (i.e. was made and then was dropped) but that there's a
specified
quote:
> time allowed to make the connection and that it's this allowed time that
is
quote:
> too short - remember, the machine is ultra busy at this time). If I am
> right in this assumption then I need a way of telling it not to give up
> quite so quickly (or to retry several hundred times). Any insight as to
> what might be happening and any suggestions concerning how to fix this
would
quote:|||> Hi GriffithsJ, try using CommandTimeout = 0.
> be most welcome.
> I include my code below (error handling and app logging removed for
> clarity):
> ' Declarations
> Dim oCmd As ADODB.Command
> Dim oCn As ADODB.Connection
> Dim lTimeOut As Long
> '--
> ' Set the timeout ( = 43200 seconds = 12 hours)
> lTimeOut = CLng(mIni.iniKeyValue(mIni.computerName, "timeOut"))
> '--
> ' Create a new connection to the database
> Set oCn = New ADODB.Connection
> With oCn
> .ConnectionString = mIni.iniKeyValue(computerName,
> "connectionString")
> .CommandTimeout = lTimeOut
> .Open
> End With
> '--
> ' Instantiate the command object
> Set oCmd = New ADODB.Command
> With oCmd
> '--
> Set .ActiveConnection = oCn
> .CommandType = adCmdText
> .CommandText = sSQL
> .CommandTimeout = lTimeOut
> '--
> ' Execute the SQL
> .Execute
> '--
> ' Disconnect command
> Set .ActiveConnection = Nothing
> '--
> End With
> '--
> ' Trash the command and connection objects
> oCn.Close
> Set oCn = Nothing
> Set oCmd = Nothing
> '--
> Many thanks in advance
> Griff
>
quote:
> --
That's the equivalent of saying infinite...
Two things.
First is that I'm always a little cautious about using infinite values, I'd
prefer to set values to very large finite values - that way, server
resources should eventually tidy themselves up.
Second is that I had a commandTimeout value of 12 hours and it died within a
minute. I'm not sure who increasing the commandTimeout value from 12 hours
to infinite would affect it being killed off in under a minute (unless
setting it to 0 has any other special effects I'm not aware of.)
Griff|||Hi Griff,
quote:
> I then create a command and use the active connection and also take the
> added precaution (which is probably pointless) of setting the command
> timeout value to also be 12 hours.
Command.CommandTimeout must be explicitly set as you did.
It is not passed over from the Connection.CommandTimeout
when assigning the Command.ActiveConnection from there.
So the taken precaution is obligatory.
quote:
> I then execute some SQL that has been dynamically prepared (and could take
> many hours to complete) and run this. However, I get an error in the
> Application event log showing that the connection timed-out. This doesn't
> happen everytime - most times it connects and runs flawlessly... The erro
r
> details for the timeout are:
You mean the c o n n e c t i o n times out, not the command, right?
When does it time out - when oCn.Open is executed or where?
If so, increase the ConnectionTimeout of oCn before Open
so that it waits longer to establish the connection:
quote:
> With oCn
> .ConnectionString = mIni.iniKeyValue(computerName,
> "connectionString")
> .CommandTimeout = lTimeOut
.ConnectionTimeout = 300 '****************************
quote:
> .Open
quote:
> End With
However, if you mean that the c o m m a n d times out at oCmd.Execute,
check whether oCmd.CommandTimeout has indeed the intended 43200 at that time
and whether it is really a command timeout or a connection error.
quote:
> Err.Number = -214746759
The error number might be wrong. Could be -2147467259 (the notorious 8000400
5H).
Regards,
Frank
Message sent 14 Jan 2004 15:19:22 GMT|||Hi Frank
Many thanks for that answer...I've made all the necessary changes as you
suggested and have uploaded the compiled code.
Will find out tomorrow morning whether this worked...
Thanks
Griff|||> Using ado/ole-db, I create a connection to a database (SQL 2000)
specifying
quote:
> a time-out value of 43200 (which should equate to 12 hours).
> I then create a command and use the active connection and also take the
> added precaution (which is probably pointless) of setting the command
> timeout value to also be 12 hours.
Comand timeout is seperate from connection timeout.
http://www.devguru.com/Technologies...andtimeout.html
Try the command GUI in a adoanywhere command gui.
http://www.adoanywhere.com/help/command.htm#Command
Mike Collier
http://www.adoanywhere.com
http://www.adoanywhere.com/forum
"GriffithsJ" <GriffithsJ_520@.hotmail.com> wrote in message
news:uzXSsXp2DHA.2060@.TK2MSFTNGP10.phx.gbl...
quote:
> Hi
> Using ado/ole-db, I create a connection to a database (SQL 2000)
specifying
quote:
> a time-out value of 43200 (which should equate to 12 hours).
> I then create a command and use the active connection and also take the
> added precaution (which is probably pointless) of setting the command
> timeout value to also be 12 hours.
> I then execute some SQL that has been dynamically prepared (and could take
> many hours to complete) and run this. However, I get an error in the
> Application event log showing that the connection timed-out. This doesn't
> happen everytime - most times it connects and runs flawlessly... The
error
quote:
> details for the timeout are:
> Err.Number = -214746759
> Err.Description = Timeout expired
> And this occurs in the region of approximately 1 minute after the code was
> initialised (the machine is ultra busy at this time).
> I may well be wrong here, but I assume that it wasn't the connection that
> timed out (i.e. was made and then was dropped) but that there's a
specified
quote:
> time allowed to make the connection and that it's this allowed time that
is
quote:
> too short - remember, the machine is ultra busy at this time). If I am
> right in this assumption then I need a way of telling it not to give up
> quite so quickly (or to retry several hundred times). Any insight as to
> what might be happening and any suggestions concerning how to fix this
would
quote:
> be most welcome.
> I include my code below (error handling and app logging removed for
> clarity):
> ' Declarations
> Dim oCmd As ADODB.Command
> Dim oCn As ADODB.Connection
> Dim lTimeOut As Long
> '--
> ' Set the timeout ( = 43200 seconds = 12 hours)
> lTimeOut = CLng(mIni.iniKeyValue(mIni.computerName, "timeOut"))
> '--
> ' Create a new connection to the database
> Set oCn = New ADODB.Connection
> With oCn
> .ConnectionString = mIni.iniKeyValue(computerName,
> "connectionString")
> .CommandTimeout = lTimeOut
> .Open
> End With
> '--
> ' Instantiate the command object
> Set oCmd = New ADODB.Command
> With oCmd
> '--
> Set .ActiveConnection = oCn
> .CommandType = adCmdText
> .CommandText = sSQL
> .CommandTimeout = lTimeOut
> '--
> ' Execute the SQL
> .Execute
> '--
> ' Disconnect command
> Set .ActiveConnection = Nothing
> '--
> End With
> '--
> ' Trash the command and connection objects
> oCn.Close
> Set oCn = Nothing
> Set oCmd = Nothing
> '--
> Many thanks in advance
> Griff
>
No comments:
Post a Comment