Query Timeout Issue with .NET

Hello all and thank you for your time.
I have an application with reports taking over 30 seconds to run. This
is producing the
"Timeout expired. The timeout period elapsed prior to completion of the
operation or the server is not responding." error. Is there a way to
increase the timeout expired default of 30 seconds until I have
resources to optimize or rewrite the querys? There are over 120
reports and querys!
I've tried:
<httpRuntime executionTimeout=3D"1800"/>
<add key=3D"XYZ"
value=3D" ;Server=3Dserver01;UID=3Dsa;PASSWORD=3Dp
assword;DATABASE=3Ddatabas=
e;Connecti=ADon
Timeout=3D 40000;"/>=20
I'd really appreciate any help.=20
SunshineI don't believe there is a connection string keyword that controls command
timeouts. You need to set the command CommandTimeout property to the
desired value.
Hope this helps.
Dan Guzman
SQL Server MVP
<sunshinevaldes@.yahoo.com> wrote in message
news:1145276070.473701.247630@.i40g2000cwc.googlegroups.com...
Hello all and thank you for your time.
I have an application with reports taking over 30 seconds to run. This
is producing the
"Timeout expired. The timeout period elapsed prior to completion of the
operation or the server is not responding." error. Is there a way to
increase the timeout expired default of 30 seconds until I have
resources to optimize or rewrite the querys? There are over 120
reports and querys!
I've tried:
<httpRuntime executionTimeout="1800"/>
<add key="XYZ"
value=" ;Server=server01;UID=sa;PASSWORD=passwor
d;DATABASE=database;Connecti_
on
Timeout= 40000;"/>
I'd really appreciate any help.
Sunshine|||Wouldn't I have to do the CommandTimeout for every call to the server?
If so that would be over a hundred so I was hoping there was something
in the web.config I could tweak. I did set the query timeout on the
server to 0 but that didn't help either.
Sunshine|||the connection timeout in your connection string indicates the timeout
for the connection to be formed, not for the actual query to complete.
This means that if for some reason it was having trouble connecting to
the database server, it would wait that long before reporting an error.
As Dan said you will need to set your CommandTimeout property in your
SQL command (I hope you've centralised your data access code or you're
in for a lot of code changes). But you should also make sure you remove
that absurd level of timeout from your connection string.
Cheers
Will|||sunshinevaldes@.yahoo.com wrote:
> Hello all and thank you for your time.
> I have an application with reports taking over 30 seconds to run.
> This
> is producing the
> "Timeout expired. The timeout period elapsed prior to completion of
> the
> operation or the server is not responding." error. Is there a way to
> increase the timeout expired default of 30 seconds until I have
> resources to optimize or rewrite the querys? There are over 120
> reports and querys!
>
> I've tried:
> <httpRuntime executionTimeout="1800"/>
> <add key="XYZ"
> value=" ;Server=server01;UID=sa;PASSWORD=passwor
d;DATABASE=database;Connect
i_on
> Timeout= 40000;"/>
>
> I'd really appreciate any help.
>
This is slightly off-topic here:
Come to this group if you want help in reducing the time it takes for your
queries to run (query optimization).
For help with client applications utilizing your queries, go to the
newsgroup that is focussed on your client application: in this case,
microsoft.public.dotnet.framework.aspnet.
My recommendation is to optimize your queries so that they don't take so
long to run. My suggestion for the interim would be to use IIS Manager to
increase the Script Timeout setting for your application. You can increase
the setting on a per-page basis by using the Server.ScriptTimeout property.
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"|||you would indeed have to set it for every call to the server. Hence my
comment about centralising data access code.
It is not possible to set this in the web.config.|||Unfortunately, the data access code is not centralized. This
application was written by consultants who say they can't fix the timed
out issue. I was hoping there was an easy fix until I can fix their
queryies.
Thanks for all your responses.
Sunshine|||Bob,
I went to microsoft.public.dotnet.framework.aspnet first but after
almost of w with no replies I came here and have had great replies
within the same day.
Can you tell me how the increase the Script Timeout in the IIS Manager?
Sunshine|||(sunshinevaldes@.yahoo.com) writes:
> Unfortunately, the data access code is not centralized. This
> application was written by consultants who say they can't fix the timed
> out issue.
Hm, if they don't enough about data-access programming to address
timeout issues properly, who says that you should be able to fix
their invoices? :-)

> I was hoping there was an easy fix until I can fix their queryies.
On SQL Server level there isn't, because this completely idiotic
default timeout of 30 seconds is set in the client API, and all SQL Server
sees is the cancellation request.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx