Solved

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

Posted on 2004-10-05
23
1,377 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
ID: 12229448
I suggest you use paging.
Your page results in timeout.

hongjun
0
 
LVL 33

Expert Comment

by:hongjun
ID: 12229463
Try

<% Server.ScriptTimeout = 300 %>

the value is in seconds
0
 
LVL 33

Expert Comment

by:hongjun
ID: 12229469
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

Author Comment

by:pavelmed
ID: 12229533
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
ID: 12229584
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
ID: 12229603
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
ID: 12229693
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
ID: 12229738
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
ID: 12229748
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
ID: 12229752
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
ID: 12229785
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
 
LVL 46

Expert Comment

by:fritz_the_blank
ID: 12229998
Occassionally, you should output the buffer--

Response.flush()


FtB
0
 
LVL 46

Expert Comment

by:fritz_the_blank
ID: 12230008
Oops, please ignore my comment as it duplicates one above.

FtB
0
 

Author Comment

by:pavelmed
ID: 12230089
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
ID: 12230124
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
ID: 12230128
it should preferably after your @Language=...
example

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

<%
...the rest here
%>
0
 

Author Comment

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

Author Comment

by:pavelmed
ID: 12230222
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
ID: 12230401
@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
ID: 12230686
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
ID: 12230756
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
ID: 12231032
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
ID: 12231047
Glad to have helped.

Regards,
peh803
0

Featured Post

Are your AD admin tools letting you down?

Managing Active Directory can get complicated.  Often, the native tools for managing AD are just not up to the task.  The largest Active Directory installations in the world have relied on one tool to manage their day-to-day administration tasks: Hyena. Start your trial today.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
SQL to update characters in table column 6 127
Microsoft SQL ADO Conn Issue 6 46
wordpress display sub menu only when click 12 38
IP API - need data... 4 23
Hello, all! I just recently started using Microsoft's IIS 7.5 within Windows 7, as I just downloaded and installed the 90 day trial of Windows 7. (Got to love Microsoft for allowing 90 days) The main reason for downloading and testing Windows 7 is t…
This demonstration started out as a follow up to some recently posted questions on the subject of logging in: http://www.experts-exchange.com/Programming/Languages/Scripting/JavaScript/Q_28634665.html and http://www.experts-exchange.com/Programming/…
Along with being a a promotional video for my three-day Annielytics Dashboard Seminor, this Micro Tutorial is an intro to Google Analytics API data.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.

770 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