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

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.
pavelmedAsked:
Who is Participating?
 
hongjunConnect With a Mentor Commented:
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
 
hongjunCommented:
I suggest you use paging.
Your page results in timeout.

hongjun
0
 
hongjunCommented:
Try

<% Server.ScriptTimeout = 300 %>

the value is in seconds
0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
hongjunCommented:
0
 
pavelmedAuthor Commented:
I don't think there is a timeout because the error is displayed almost instantly.  The server is pretty powerful.
0
 
peh803Connect With a Mentor Commented:
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
 
pavelmedAuthor Commented:
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
 
pavelmedAuthor Commented:
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
 
peh803Commented:
Please see my suggestions in this related post from yesterday:

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

regards,
peh803
0
 
peh803Commented:
If you would like me to expand at all, please just ask.  I'm happy to help further!

Regards,
peh803
0
 
peh803Commented:
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
 
fritz_the_blankCommented:
Occassionally, you should output the buffer--

Response.flush()


FtB
0
 
fritz_the_blankCommented:
Oops, please ignore my comment as it duplicates one above.

FtB
0
 
pavelmedAuthor Commented:
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
 
pavelmedAuthor Commented:
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
 
hongjunCommented:
it should preferably after your @Language=...
example

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

<%
...the rest here
%>
0
 
pavelmedAuthor Commented:
I don't have @Language=VBScript on the page since this is a default for ASP
0
 
pavelmedAuthor Commented:
It works both ways: before the #include file and after it.
Why should it be placed on the top?

Thanks
0
 
peh803Commented:
@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
 
pavelmedAuthor Commented:
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
 
peh803Commented:
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
 
pavelmedAuthor Commented:
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
 
peh803Commented:
Glad to have helped.

Regards,
peh803
0
All Courses

From novice to tech pro — start learning today.