469,271 Members | 1,787 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,271 developers. It's quick & easy.

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.


Apr 17 '06 #1
3 1951
Raj wrote:
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...andtimeout.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...a43b1abef.asp).

Bob Barrows
--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"
Apr 17 '06 #2
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]" wrote:
Raj wrote:
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...andtimeout.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...a43b1abef.asp).

Bob Barrows
--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

Apr 18 '06 #3

..

Raj wrote:
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
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.
Apr 18 '06 #4

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

17 posts views Thread by Achim Domma | last post: by
4 posts views Thread by Cath B | last post: by
5 posts views Thread by Jason | last post: by
9 posts views Thread by tshad | last post: by
5 posts views Thread by supercooper | last post: by
2 posts views Thread by Robin Becker | last post: by
1 post views Thread by Scorpion657 | last post: by
2 posts views Thread by Andrew Cooper | last post: by
reply views Thread by zhoujie | last post: by
reply views Thread by suresh191 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.