Timeout error - ASP 0113

[JSMITH242B]

Hello All,
I have an ASP application which has a SQL Server backend. The query processed is parameter driven and is based on the users' selection.
If a user queries a small amount of data, this is fine, the results are displayed within seconds however if the users query is for a wide arrange of records, I get a script timeout error - 'ASP 0113' and a message saying that the maximum time for a script to be executed can be changed by specifying a new value for the property Server.ScriptTimeOut.

Where is this value set?

The IIS Administrator has said that the timeout value has been set to 15 minutes on the IIS Server. The query times out after about 2 minutes.

TIA
J

 

[simonchristieis]

Place this code in ther page where the sql is run:

<% Server.ScriptTimeout = 600 %>

600 = seconds

to find out your current script timeout setting:

<%=Server.ScriptTimeout%>

hth

simon

 

[JSMITH242B]

Hi Simon,
I tried this. The script runs to the scripttimeout value and then produces the error.
I set the Server.ScriptTimeout 900. It ran for 15 minutes then gave the error.

Where could the error lie?

TIA
J

 

[simonchristieis]

There are 2 ways to approach this - I could randomly suggest errors that could possibly occur - or you could post the code.

It would seem to me that you have a loop that is not exiting.

 

[JSMITH242B]

OK Simon...
Here is the SQL statement:
SELECT left(ExportData.dbo.tblWeeklySales.wsrCusNum,5) AS AccountNo,
ExportData.dbo.tblWeeklySales.wsrCusName as Client ,
SUM ( CASE WHEN DATEPART(yy,ExportData.dbo.tblWeeklySales.wsrWERun) = 2002
THEN ExportData.dbo.tblWeeklySales.wsrSales ELSE 0 END) AS Turnover2002,
SUM ( CASE WHEN DATEPART(yy,ExportData.dbo.tblWeeklySales.wsrWERun) = 2003
THEN ExportData.dbo.tblWeeklySales.wsrSales ELSE 0 END ) AS Turnover2003,
SUM (CASE WHEN DATEPART(yy,ExportData.dbo.tblWeeklySales.wsrWERun) = 2002
THEN CONVERT(decimal(9,2),(ExportData.dbo.tblWeeklySales.wsrSales) - (ExportData.dbo.tblWeeklySales.wsrProfit), 2)ELSE 0 END) AS Cost2002,
SUM (CASE WHEN DATEPART(yy,ExportData.dbo.tblWeeklySales.wsrWERun) = 2003 THEN CONVERT(decimal(9,2),(ExportData.dbo.tblWeeklySales.wsrSales) - (ExportData.dbo.tblWeeklySales.wsrProfit), 2)ELSE 0 END) AS Cost2003,
SUM ( CASE WHEN DATEPART(yy,ExportData.dbo.tblWeeklySales.wsrWERun) = 2002 then ExportData.dbo.tblWeeklySales.wsrProfit ELSE 0 END) AS Profit2002, SUM ( CASE WHEN DATEPART(yy,ExportData.dbo.tblWeeklySales.wsrWERun) = 2003
then ExportData.dbo.tblWeeklySales.wsrProfit ELSE 0 END) AS Profit2003, CONVERT( decimal(9,2),
CASE WHEN SUM( CASE WHEN DATEPART(yy,ExportData.dbo.tblWeeklySales.wsrWERun) = 2002
then ExportData.dbo.tblWeeklySales.wsrSales ELSE 0 END ) != 0
THEN SUM( CASE WHEN DATEPART(yy,ExportData.dbo.tblWeeklySales.wsrWERun) = 2002
then ExportData.dbo.tblWeeklySales.wsrProfit ELSE 0 END ) * 100 / SUM( CASE WHEN DATEPART(yy,ExportData.dbo.tblWeeklySales.wsrWERun) = 2002
then ExportData.dbo.tblWeeklySales.wsrSales ELSE 0 END ) ELSE 0 END , 2 ) AS MargPer2002, CONVERT( decimal(9,2),
CASE WHEN SUM( CASE WHEN DATEPART(yy,ExportData.dbo.tblWeeklySales.wsrWERun) = 2003
then ExportData.dbo.tblWeeklySales.wsrSales ELSE 0 END ) != 0
THEN SUM( CASE WHEN DATEPART(yy,ExportData.dbo.tblWeeklySales.wsrWERun) = 2003
then ExportData.dbo.tblWeeklySales.wsrProfit ELSE 0 END ) * 100 / SUM( CASE WHEN DATEPART(yy,ExportData.dbo.tblWeeklySales.wsrWERun) = 2003 then ExportData.dbo.tblWeeklySales.wsrSales ELSE 0 END ) ELSE 0 END , 2 ) AS MargPer2003
FROM UserID2.dbo.tblRegion
INNER JOIN UserID2.dbo.tblArea
ON UserID2.dbo.tblRegion.regRegionID = UserID2.dbo.tblArea.areRegionID
INNER JOIN UserID2.dbo.tblBranch
ON UserID2.dbo.tblArea.areAreaID = UserID2.dbo.tblBranch.brnAreaID INNER JOIN ExportData.dbo.tblWeeklySales
ON RIGHT(USERID2.dbo.tblBranch.brnBranchNumber, 3) = RIGHT(ExportData.dbo.tblWeeklySales.wsrCusBrn, 3)
WHERE (DatePart(m,ExportData.dbo.tblWeeklySales.wsrWERun) >= 1 AND DatePart(m,ExportData.dbo.tblWeeklySales.wsrWERun) <= 1)AND (DATEPART(yy,ExportData.dbo.tblWeeklySales.wsrWERun) < = 2003)AND (DATEPART(yy,ExportData.dbo.tblWeeklySales.wsrWERun) > = 2002)GROUP BY left(ExportData.dbo.tblWeeklySales.wsrCusNum,5), ExportData.dbo.tblWeeklySales.wsrCusName ORDER BY left(ExportData.dbo.tblWeeklySales.wsrCusNum,5) asc


This statement returns 4936 reords and is parameter driven.
If I select less records, it runs fine. It takes 10 secs to run in the Query Analyser.

regards
J

 

[simonchristieis]

This doesn't look like asp code to me - perhaps you should post into the sql forum.

 

[JSMITH242B]

Sorry Simon...
Here is my code which I use to do my connection to the SQL Server Database.

sub getDisplayArray(sSQL, sreport, irows, icols, arrData, arrDisplay)
'uses the Class csDBController to build the array into the correct format
'Build the display array from the SQL string passed
set dbcontroller = new csDBController
with dbcontroller
.DBConnection = strWSA
.MakeConnection
.SQLString = sSQL

'the number of rows returned from the SQL statement
irows = .CreateArray(arrData, icols)
if irows > 0 then
if right(strPhysicalFolder,1) <> "\" then strPhysicalFolder = strPhysicalFolder & "\"
sname = strPhysicalFolder & .GetTemplateName(sreport)
redim arrDisplay(icols, irows)
.TransposeNewArray arrData, arrDisplay, irows, icols, datetype
end if
.CloseConnection
end with
set dbcontroller = nothing
end sub


TIA
J

 

[JSMITH242B]

The CreateArray procedure looks like thsi..

public function CreateArray(arrData, icols)
'Creates an array from the SQL property and the currect connection property
Dim rst, irows
'Default the rows to zero
irows = 0
'Create the recordset
set rst = server.CreateObject("ADODB.Recordset")
rst.Open SQLString, Connection,adOpenKeyset,adLockOptimistic '240304
'Check to see if any data is in the recordset
if not rst.eof then
rst.movelast
rst.movefirst
irows = rst.recordcount
icols = rst.fields.Count - 1
'redimensions the array to the exact same size as the recordset
'this way no additional resources are need to populate all the data from the array
Redim arrData(irows, icols)
irows = 0
do while not rst.eof
for i = 0 to icols
arrData(irows, i) = cstr(rst.fields(i).value)
next
irows = irows + 1
rst.movenext
loop
icols = icols + 1
end if
set rst = nothing
CreateArray = irows
end function

Thanks
J