Timeout expired Problem

Question

Post reply

Timeout expired Problem

Kanwar A Singh

Old Hand

Points: 336
More actions
October 7, 2004 at 11:06 am

#86437
I m facing a problem of timeout expired.
If I execute stored procedure through Asp Page, which returns approximately 200000 rows, my ASP page always gives error “Script time out” after 40 Seconds.

Error Type:
Microsoft OLE DB Provider for ODBC Drivers (0x80040E31)
[Microsoft][ODBC SQL Server Driver]Timeout expired

But if I insert the required rows into a table through SP and then if I use “Select * from Table” in my ASP page it works fine.
But I cant use this solution in multi-user enjoinment.
```
I have tried Server.ScriptTimeout = NumSeconds but it did’t helped
```
Any suggestions?

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

fernando.pires@gustavsberg.com SSC Journeyman Points: 96 More actions · Answer 1

Change the setting on database level.

Properties -> Connections

Remote properties

But 200.000 rows ?

Gordon Pollokoff SSCommitted Points: 1930 More actions · Answer 2

You said you tried Server.ScriptTimeout = numseconds but did you try Server.ScriptTimeout = 0 seconds. This should effectively turn ScriptTimeout off.

Gordon Pollokoff

"Wile E. is my reality, Bugs Bunny is my goal" - Chuck Jones

fernando.pires@gustavsberg.com SSC Journeyman Points: 96 More actions · Answer 3

This has nothing to do with scripttimeout. But the time SQL Server thinks the query is estimated to run.

N. Subramanian SSC Enthusiast Points: 178 More actions · Answer 4

increase the time in HKLM for odbc timeout....

the default is 60 seconds....

in your case make it 300 seconds... then u will not get timeout error...

mike gercevich SSCrazy Points: 2961 More actions · Answer 5

This looks to be a Command timeout. The default is 30 seconds. You can increase this on the command object before you call your execute method.

objCmd.CommandTimeout = 90 'in seconds

Kanwar A Singh Old Hand Points: 336 More actions · Answer 6

Seting command Time out solves my problem.Lots of thanks for that

Now i am dealing with another problem.

With command object i could not open recordset with Dynamic cursor, the recordset is farward only.but i need to move first, after reaching the EOF.For the time being I am using recordset.reQuery, which takes much longer.

any clue to open Dynamic recordset with command object.

code:-

'*************************************

Dim adoCmd

Set adocmd = Server.CreateObject("ADODB.Command")

with adocmd

.CommandText = "_sp_repSmplMgmt_CSDEvent"

.ActiveConnection = adoConnection

.CommandType = adCmdStoredProc

.Parameters.Append .CreateParameter ("group", adChar, adParamInput, 25, strTerritory)

.Parameters.Append .CreateParameter ("productlist", advarchar, adParamInput, 255, strProductList)

.Parameters.Append .CreateParameter ("beginDate", advarchar, adParamInput,10,dtmBeginDate)

.Parameters.Append .CreateParameter ("advarchar", advarchar, adParamInput,10, dtmEndDate)

.Parameters.Append .CreateParameter ("Format", adInteger, adParamInput,4, strFormatType)

end with

Set rsEvent = CreateObject("ADODB.Recordset")

adoCmd.CommandTimeout=12000

adocmd.Execute

rsEvent.Open adocmd ,,adOpenDynamic

Do While not rsEvent.eof

rsEvent.MoveNext

loop

rsEvent.MoveFirst 'This Line gives me error

Response.End

'*************************************

Kanwar A Singh Old Hand Points: 336 More actions · Answer 7

Any body having any clue how to open Dynamic Recordset with command object?