procedure timeout for report

[Keith]

We have many reports in an adp (wiht MS SQL 2005) that have code in them
that's similar to the following to create the dataset for the report (in the
OnOpen event):

Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset
Dim sSql As String


sSql = "EXEC sp_RptWorkoutCounts '" _
& strGetParentAttyIDs & "', '" _
& Replace(strGetCounties, "'", "''''") & "', '" _
& Format(dteStartDate, "mm/dd/yyyy") & "', '" _
& Format(dteEndDate, "mm/dd/yyyy") & "'"


rs.Open sSql, cnnCurrProj, adOpenStatic

Me.RecordSource = rs.Source

DoCmd.Maximize

rs.Close

Problem is that one report is now timing out after 30 seconds. I know how to
set timeouts using the adodb command object when running for example a
stored procedure that adds a new row to a table, but this is different. The
code above is getting the source for the report and it's always worked
great. In fact I'm not sure how to use the other method for report data. So
anyway, my question is how do I set the timeout to say 2 minutes for the
above?

I thought I'd do this before the rs.Open line:

CurrentProject.Connection.CommandTimeout = 120

and then reset it to 30 after the report is done but that seems wrong to me.


Thanks,

Keith

 

[Sylvain Lafontaine]

You must set the timeout on the connection object used for opening the
recordset, which seems to be cnnCurrProj here. Depending on how you have
declared and setted this variable, this could be the same thing as the
CurrentProject.Connection object or not (did you forget to use the SET
keyword before the assignation?).

Second, you should remove the prefix sp_ from the use of your SP, as this
prefix has a special signification to SQL-Server, will reduce the
performance and could lead to some subtil bug in some cases when not used
adequately.

Finally, you should take a look to see if it's normal for your SP to take 30
seconds or more to execute. Clearing the caches, updating the statistics
and/or reindexing could help; especially if this SP is now executing with
some bad query plan. Adding "With Recompile" to the declaration of the SP
could be a helpful test here.

--
Sylvain Lafontaine, ing.
MVP - Windows Live Platform
Email: sylvain2009 sylvainlafontaine com (fill the blanks, no spam please)
Independent consultant and remote programming for Access and SQL-Server
(French)

 

[Keith]

okidoke. Thanks for the info.

You must set the timeout on the connection object used for opening the
recordset, which seems to be cnnCurrProj here. Depending on how you have
declared and setted this variable, this could be the same thing as the
CurrentProject.Connection object or not (did you forget to use the SET
keyword before the assignation?).

Second, you should remove the prefix sp_ from the use of your SP, as this
prefix has a special signification to SQL-Server, will reduce the
performance and could lead to some subtil bug in some cases when not used
adequately.

Finally, you should take a look to see if it's normal for your SP to take
30 seconds or more to execute. Clearing the caches, updating the
statistics and/or reindexing could help; especially if this SP is now
executing with some bad query plan. Adding "With Recompile" to the
declaration of the SP could be a helpful test here.

--
Sylvain Lafontaine, ing.
MVP - Windows Live Platform
Email: sylvain2009 sylvainlafontaine com (fill the blanks, no spam please)
Independent consultant and remote programming for Access and SQL-Server
(French)

 

[Paul Shapiro]

Here is the code I use for increasing the timout to output long-running
queries:
Const cstrTimeoutOptionName As String = "OLE/DDE Timeout (Sec)"
Const clngTimeoutSecondsForQuery As Long = 300 'was 60
'Temporarily increase query timeout, which is an application-wide setting
'CurrentProject.Connection.CommandTimeout = 60 'Too late to change this
setting- no effect
strQueryTimeOutOriginal =
Application.GetOption(OptionName:=cstrTimeoutOptionName)
Application.SetOption cstrTimeoutOptionName,
CStr(clngTimeoutSecondsForQuery)
DoCmd.OutputTo _
acOutputServerView, _
strQueryName, _
"MicrosoftExcel(*.xls)", _
strExcelFilePathAndName, _
True
Application.SetOption cstrTimeoutOptionName, strQueryTimeOutOriginal