My ASP.NET shows error 404 because can't wait for SQL Procedure result


I have ASP.NET Application. It gets data (report) from SQL Server 2008.
The database is configured to get data updates from other DB servers.

So my problem is:
my application drops error 404 page because SQL query takes 30-60 seconds.
I set Connection Timeout=120.
What else should I do ?

Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Alpesh PatelAssistant ConsultantCommented:
404 for "Page Not Found". Please make sure page you are requesting is exist.
exceterAuthor Commented:
Well, the page exists, even I set  <customErrors mode="RemoteOnly" defaultRedirect="ErrRequest.aspx" />
so that users not see 404 Page.

 That it not the case.

The problem is - How to make my application wait for SQL Procedure result, and not throw error ?

The error occurs because of that. All the pages are exist, just it does not wait for queru result.
Alfred A.Commented:
Have you tried increasing the Connection Timeout further to let say 600 for example?  120 might not be enough.

Have you also tried to optimised your data retrieval to reduce the wait time (e.g. setting up indexes, etc.).?  It is possible that you might have some missing indexes and by putting this indexes, it might speed up your data retrieval.

Also, you could try to put a try-catch block at the point data retrieval and re-execute it again in the catch if the error occurred first time.

I hope this helps.
I have dealt with this kind of issue in the past.  The solution is to run the procedure asynchronously.  

Have your resultset put into a holding table with an extra ID column.
Have your procedure insert data into that holding table rather than to a dataset on your webpage.
You have your page kick off the proc and gives it a big long random ID number 98282928122334, but just continue on to the user without waiting, displaying a page with a little hourglass or spinner.
You have a javascript on that page which calls a simple generic handler (ashx) page that checks the temp table to see if there is a recordset with the id 98282928122334.  If there is, then the script redirects to a page that displays the dataset using a simple select from the temp holding table.  (or you can just pull the dataset right into a div on the current page).

Anyway, people don't mind waiting as long as they know that the work is being done.  Also, if you keep extending the timeout, then people are just going to start refreshing that page and kicking off that massive SP again and again.

Good luck.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
exceterAuthor Commented:
Have you tried increasing the Connection Timeout further to let say 600 for example?  120 might not be enough.
I even tried to set this value to 1000, but ASP.NET drops error after 5-20 seconds.

Hmm, it sounds logically, but not the required solution in my case (I hope so).

I will try to reindex the DB again, will see what happens.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
.NET Programming

From novice to tech pro — start learning today.