Timeout Error in ASP

[Raj]

Hi,

I'm getting the Timeout error in one of my ASP application. I'm calling a
slight complex stored procedure which is taking bet 35 to 40 seconds when run
in the backend which is not bad. But when it is called from the ASP code it
is giving the following error message:

Microsoft OLE DB Provider for SQL Server error '80040e31'
Timeout expired

Following is the code I'm using to call the stored proc.:
Set cmd = Server.CreateObject("ADODB.Command")
cmd.ActiveConnection = conn
cmd.CommandText = "Sel_MOD_Changes"
Set rs_Item = cmd.Execute(recsAffected, Array(POSEQNUM),
adCmdStoredProc)

The software configuration we are using are: Windows 2000 service pack 4 and
SQL Server 2000 for database. I've tried incresing the Connection time &
Script time in IIS but it did not work. I'm told we need to install some
patch to solve this problem, I'm not sure. Can any of you suggest any
solution for this.

Thanks,
Raj.

 

[Bob Barrows [MVP]]

Hi,

I'm getting the Timeout error in one of my ASP application. I'm
calling a slight complex stored procedure which is taking bet 35 to
40 seconds when run in the backend which is not bad.

Well ... maybe it's "not bad" in the backend, but it's going to kill your
web server's performance.

But when it is
called from the ASP code it is giving the following error message:

Microsoft OLE DB Provider for SQL Server error '80040e31'
Timeout expired

Following is the code I'm using to call the stored proc.:
Set cmd = Server.CreateObject("ADODB.Command")
cmd.ActiveConnection = conn
cmd.CommandText = "Sel_MOD_Changes"
Set rs_Item = cmd.Execute(recsAffected, Array(POSEQNUM),
adCmdStoredProc)

The software configuration we are using are: Windows 2000 service
pack 4 and SQL Server 2000 for database. I've tried incresing the
Connection time & Script time in IIS but it did not work. I'm told we
need to install some patch to solve this problem, I'm not sure. Can
any of you suggest any solution for this.

Thanks,
Raj.

Your best course of action is to optimize the procedure so it does not take
so long to run. I would suggest reading www.aspfaq.com/5006 and posting the
relevant details to .sqlserver.programming so someone can help you optimize
the procedure.

In the meantime, you can set the Connection object's CommandTimeout property
(http://msdn.microsoft.com/library/en-us/ado270/htm/mdprocommandtimeout.asp)
to a number that's high enough to allow this procedure to run (you may need
to set the Server object's ScriptTimeout property as well -
http://msdn.microsoft.com/library/en-us/iissdk/html/429c1800-7d19-4011-a1ca-482a43b1abef.asp).

Bob Barrows

 

[Raj]

I've already tried the commandtimeout and scripttimeout but it did not work.
As far as modifying the stored proc is concerned I've alredy referred it to
our DBA and it can not be furthrt modified based on our business logic. When
I'm searching some sites some suggestions are to upgrade the MDAC to the
latest version. But I'm not sure how to do this and whether doing this will
effect anything else. Do you've any idea on this.

Thanks.

 

[Bob Barrows [MVP]]

..

I've already tried the commandtimeout and scripttimeout but it did
not work.

When you say you tried those, did it make any difference? Different error
messages? Anything? If not, then you did not provide a large enough value
for the timeouts.

Show the code where you implemented them.

As far as modifying the stored proc is concerned I've
alredy referred it to our DBA and it can not be furthrt modified
based on our business logic.

Perhaps your DBA could use some assistance ...
It's not always a modification to the stored procedure that can make a
difference.

When I'm searching some sites some
suggestions are to upgrade the MDAC to the latest version. But I'm
not sure how to do this and whether doing this will effect anything
else. Do you've any idea on this.

There are no MDAC upgrades that I know of that will affect this issue.
However, just go to the Microsoft website and search for the MDAC downloads
... it's not hard. Just download the latest version and install it.

Bob Barrows