|
Home > Archive > IIS ASP > April 2006 > Timeout Error in ASP
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
Timeout Error in ASP
|
|
|
| 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] 2006-04-27, 7:51 am |
| 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/e...mandtimeout.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/e...82a43b1abef.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"
| |
|
| 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:
>
> Well ... maybe it's "not bad" in the backend, but it's going to kill your
> web server's performance.
>
>
> 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/e...mandtimeout.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/e...82a43b1abef.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"
>
>
>
| |
| Bob Barrows [MVP] 2006-04-27, 7:51 am |
|
..
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.
|
|
|
|
|