shellyahoo71
asked on
][ODBC SQL Server Driver]Timeout expired
I have a 17GB database in the SQL Server.
I have a stored procedure
After I put all the parameters and tried to run the output page.
I got the following error:
Microsoft OLE DB Provider for ODBC Drivers error '80040e31'
[Microsoft][ODBC SQL Server Driver]Timeout expired
/reference/list.asp, line 42
line 42 is the "cmdcp.Execute"
I am using SQL Server 7
the version of the SQL service pack is 3.700..
ODBC Connection was ok after verified.
the store procedure was ok when I run it from the SQL Server and it returns the result.
But when I run it from the web. It gave me the timeout expired.
I am thinking about the client side has the timeout problem. I monitor the SQL Server when I run that page, after I got the timeout message, the SQL Server was still executing the query.
Is that a possible way to set a timeout variable? If so, Where ?
Will it be in global.asa??
Please Help!!
I have a stored procedure
After I put all the parameters and tried to run the output page.
I got the following error:
Microsoft OLE DB Provider for ODBC Drivers error '80040e31'
[Microsoft][ODBC SQL Server Driver]Timeout expired
/reference/list.asp, line 42
line 42 is the "cmdcp.Execute"
I am using SQL Server 7
the version of the SQL service pack is 3.700..
ODBC Connection was ok after verified.
the store procedure was ok when I run it from the SQL Server and it returns the result.
But when I run it from the web. It gave me the timeout expired.
I am thinking about the client side has the timeout problem. I monitor the SQL Server when I run that page, after I got the timeout message, the SQL Server was still executing the query.
Is that a possible way to set a timeout variable? If so, Where ?
Will it be in global.asa??
Please Help!!
ASKER
I still got the same error, any idea?
Server.ScriptTimeout = 1600
connData.CommandTimeout= 1000
Set objAnswer = Server.CreateObject ("ADODB.Recordset")
Server.ScriptTimeout = 1600
connData.CommandTimeout= 1000
Set objAnswer = Server.CreateObject ("ADODB.Recordset")
What the execution time within SQL Query Analyzer? How much data you try to select and publish... I hope not 17 Megs? :)
ASKER
Adjusted points from 50 to 60
ASKER
ATM
The stored procedure will search for 2 tables and get the data
There are around 50 fields come out as a result
and the result should not exceed 100 records
I only need several fields at the moment.
The whole file in 17 GB
I am not sure how big of these 2 tables.
The stored procedure will search for 2 tables and get the data
There are around 50 fields come out as a result
and the result should not exceed 100 records
I only need several fields at the moment.
The whole file in 17 GB
I am not sure how big of these 2 tables.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Sorry ATM
don't be offended... :~(
I really need to solve this problem.
It took about around 60-90 seconds to run the query.
But like I said
I have set
Server.ScriptTimeout = 1600
connData.CommandTimeout= 1000
still got the timeout expired..
Help, help... Help.. :~(
don't be offended... :~(
I really need to solve this problem.
It took about around 60-90 seconds to run the query.
But like I said
I have set
Server.ScriptTimeout = 1600
connData.CommandTimeout= 1000
still got the timeout expired..
Help, help... Help.. :~(
ASKER
ATM
Another quick question,
do you think the index of the reference field will help?
Another quick question,
do you think the index of the reference field will help?
Of course make index for WHERE and ORDER BY fields.
How do You open recordset?
You can create stored procedure which returns recordset. And open it as Recordset:
Set commandObj=Server.CreateOb ject("ADOD B.Command" )
commandObj.ActiveConnectio n=yourConn
commandObj.CommandTimeout= 60
commandObj.CommandType=4
commandObj.CommandText="yo urSPName"
commandObj.Parameters.Appe nd commandObj.CreateParameter ("param1", adInteger,adParamInput,10, some_param )
Set rsObj=commandObj.Execute()
or
Set rsObj=Server.CreateObject( "ADODB.Rec ordset")
rsObj.Cursortype adForwardOnly
rsObj.Open commandObj
....
so to open recordset, commandtimeout will be used.
How do You open recordset?
You can create stored procedure which returns recordset. And open it as Recordset:
Set commandObj=Server.CreateOb
commandObj.ActiveConnectio
commandObj.CommandTimeout=
commandObj.CommandType=4
commandObj.CommandText="yo
commandObj.Parameters.Appe
Set rsObj=commandObj.Execute()
or
Set rsObj=Server.CreateObject(
rsObj.Cursortype adForwardOnly
rsObj.Open commandObj
....
so to open recordset, commandtimeout will be used.
1. For executed asp script:
Server.ScriptTimeOut=XX (sec.)
2. For executed stored procedure
Set commandObj=Server.CreateOb
commandObj.CommandTimeout=
Generally script's timeout +3-4 sec than command's.
Also its possible to set this values statically for all scripts within server, via
REGISTRY settings or ODBC settings (if You use this one).
Hope this help.