Timeout expiration

[teuf]

I have created an ADP project with an sqlServer 2000 db executing long
queries or strored proc.

After, near 30 seconds, my work is interrupted and I get "Delay Expired"
message.

I've tried my stored proc in Query Analyser, it works fine.

Could you tell me how to increase this timeout?

 

[Guy Horton]

Teuf,

Three methods...

1) Increase the default timeout... Tools/Options/Advanced OLE/DDE timeout
(sec).
(max 300 seconds)

2) You don't say how you execute your procedures from within the adp. The
following is a programatic example, setting the commandtimeout = 0 means no
timeout (execute until procedure completes)

Dim cmd as ADODB.Command
Set cmd = New ADODB.Command
With cmd
.ActiveConnection = CurrentProject.Connection
.CommandType = adCmdStoredProc
.CommandText = "spLongRunningStoredProcedure"
.CommandTimeout = 0
' ADO will wait until execution is complete
.Parameters.Append .CreateParameter("@Id", adInteger, adParamInput)
.Parameters("@Id") = Me!txtId
.Execute , , adExecuteNoRecords
End With
Set cmd = Nothing

3) If possible tune your queries so that they don't take so long to run

Hope this helps
Guy