Solved

HTTP 500 error while processing a large table (more than 2000 rows)

Posted on 2004-10-05
23
1,369 Views
Last Modified: 2008-02-01
I am getting HTTP 500 error while processing a large table (more than 2000 rows).

The ASP application has to process a large table that is filled by the data queried from the Oracle DB.
The table has 8 columns.  
The SQL query returns more than 2000 records, so the table should have more then 2000 rows.

When the ASP page processes such data, it returns the HTTP 500 – Internal server error.
But when I decrease the number of rows to be displayed in the table to less than 2000 (1900), the table is displayed OK.

How can I resolve this problem or find a work-around?

Thanks a lot.
0
Comment
Question by:pavelmed
  • 9
  • 7
  • 5
  • +1
23 Comments
 
LVL 33

Expert Comment

by:hongjun
Comment Utility
I suggest you use paging.
Your page results in timeout.

hongjun
0
 
LVL 33

Expert Comment

by:hongjun
Comment Utility
Try

<% Server.ScriptTimeout = 300 %>

the value is in seconds
0
 
LVL 33

Expert Comment

by:hongjun
Comment Utility
0
 

Author Comment

by:pavelmed
Comment Utility
I don't think there is a timeout because the error is displayed almost instantly.  The server is pretty powerful.
0
 
LVL 19

Assisted Solution

by:peh803
peh803 earned 100 total points
Comment Utility
Please go to tools --> internet options --> advanced tab and make sure that "Show friendly HTTP error messages" is UNCHECKED (under the browsing group).  Chances are some other kind of error is happening, but you're not getting any useful notification because this value is turned on.

Hope this helps,
peh803
0
 

Author Comment

by:pavelmed
Comment Utility
Just in case I included <% Server.ScriptTimeout = 300 %> as longlun suggested.  It did not help.  As I indicated before, the error is displayed in less than half second, so there is no timeout.

Thanks.
0
 

Author Comment

by:pavelmed
Comment Utility
I unchecked the "Show friendly HTTP error messages" in the internet options --> advanced tab, and now I see the following error:
*********************************************************************
Response object error 'ASP 0251 : 80004005'

Response Buffer Limit Exceeded

/MySystem/MyFile.asp, line 0

Execution of the ASP page caused the Response Buffer to exceed its configured limit.
**********************************************************************

This does look more descriptive.

I would appreciate the suggestions how to deal with it.

Thanks.

0
 
LVL 19

Expert Comment

by:peh803
Comment Utility
Please see my suggestions in this related post from yesterday:

http://experts-exchange.com/Web/Web_Languages/ASP/Q_21155464.html

regards,
peh803
0
 
LVL 33

Accepted Solution

by:
hongjun earned 400 total points
Comment Utility
Try this
Put this on top of your page


<%
Response.Buffer=true
%>


Then in the loop where you display records, you add this

'''start loop



Response.Flush
'''end loop



DB Search with Paging
http://www.asp101.com/samples/db_paged_search.asp

Display Recordset Data in a Paged Fashion
http://gethelp.devx.com/techtips/asp_pro/10min/10min0100/10min0100.asp

Optimize and Enhance the Paged Recordset Techniques
http://gethelp.devx.com/techtips/asp_pro/10min/10min0300/10min0300.asp

Paged Table Displays by Charles Carroll & Jeff Emrich
http://www.learnasp.com/learn/dbtablepaged.asp

Paging Through Records from ASP
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnserv/html/server05222000.asp

COMPARASION OF SEVERAL APPROACHES FOR DB PAGING:

Paging: Use ADO, getrows, or a Stored Procedure?
http://www.15seconds.com/issue/010308.htm

If you are using SQL Server, stored procedures offer the best approach.  The SP the most efficient approach because, unlike ADO and getrows which both return the entire set of records to the Web server, the stored procedure returns only the records that are needed for the current page.


hongjun
0
 
LVL 19

Expert Comment

by:peh803
Comment Utility
If you would like me to expand at all, please just ask.  I'm happy to help further!

Regards,
peh803
0
 
LVL 19

Expert Comment

by:peh803
Comment Utility
hongjun makes a very good point -- you should probably avoid returning HUGE datasets to your user and rather you should probably paginate your results (users can get a bit annoyed about having to wait long times, etc).  He has provided some great methods / examples / resources for adding this functionality.  What I have provided will simply help with the response buffer limit exceeded error.

Thanks,
peh803
0
Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

 
LVL 46

Expert Comment

by:fritz_the_blank
Comment Utility
Occassionally, you should output the buffer--

Response.flush()


FtB
0
 
LVL 46

Expert Comment

by:fritz_the_blank
Comment Utility
Oops, please ignore my comment as it duplicates one above.

FtB
0
 

Author Comment

by:pavelmed
Comment Utility
Thank you all for quick and useful answers.  Since I have a very limited time to launch this app, I am leaning to accept longjun's answer.  I have checked it, and it worked.  

longjun,

I have an #include file ...  at the top of the page.
I placed the "Response.Buffer=true" below the include file, and it worked.  But since you indicated to include it at the very top of the page, should I put it above the #include file... ?

Thank you
0
 

Author Comment

by:pavelmed
Comment Utility
I forgot to mention that to decrease the display time, I used Response.flush after each 500 rows, so it practically does not slow the page.
Thanks again
0
 
LVL 33

Expert Comment

by:hongjun
Comment Utility
it should preferably after your @Language=...
example

<%@Language=VBScript%>
<%
Response.Buffer=true
%>

<%
...the rest here
%>
0
 

Author Comment

by:pavelmed
Comment Utility
I don't have @Language=VBScript on the page since this is a default for ASP
0
 

Author Comment

by:pavelmed
Comment Utility
It works both ways: before the #include file and after it.
Why should it be placed on the top?

Thanks
0
 
LVL 19

Expert Comment

by:peh803
Comment Utility
@pavelmed:

>>"Since I have a very limited time to launch this app, I am leaning to accept longjun's answer"

hongjun's answer is the "nice" and "correct" way to do it, so this is a good choice.  If, however, you need to fix the response buffer limit exceeded error, I have provided the information you need for that.  Either way, let me know if I can help further.

Thank you,
peh803
0
 

Author Comment

by:pavelmed
Comment Utility
pen803,
The response buffer had been already increased earlier because of another problem from 200 KB to 1 MB, and I decided not to increase it further, but use the Response.Flush instead.

Thanks
0
 
LVL 19

Expert Comment

by:peh803
Comment Utility
pavelmed -- good decision.  It sounds like you're taking the best approach here.

Let me know if I can provide any further help.

Thanks,
peh803
0
 

Author Comment

by:pavelmed
Comment Utility
OK,

I am accepting in general the longjun's suggestions to flush the buffer (with some modifications), but the suggestion from pen803 to uncheck the "Show friendly HTTP error messages" option was very helpful to specify the problem's cause.

So I am splitting the points for longjun and pen803 as 400:100

Thank you all
0
 
LVL 19

Expert Comment

by:peh803
Comment Utility
Glad to have helped.

Regards,
peh803
0

Featured Post

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Syntax Help on SP 4 52
Forcing form refresh - classic ASP 7 46
JSON error 4 38
XML Parsing Classic ASP 5 42
I recently decide that I needed a way to make my pages scream on the net.   While searching around how I can accomplish this I stumbled across a great article that stated "minimize the server requests." I got to thinking, hey, I use more than one…
Have you ever needed to get an ASP script to wait for a while? I have, just to let something else happen. Or in my case, to allow other stuff to happen while I was murdering my MySQL database with an update. The Original Issue This was written…
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
This tutorial demonstrates a quick way of adding group price to multiple Magento products.

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now