asked on

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.

hongjun

I suggest you use paging.
Your page results in timeout.

hongjun

hongjun

Try

<% Server.ScriptTimeout = 300 %>

the value is in seconds

hongjun

Paging
www.asp101.com/samples/db_paging

pavelmed

ASKER

I don't think there is a timeout because the error is displayed almost instantly. The server is pretty powerful.

SOLUTION

peh803

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

pavelmed

ASKER

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.

pavelmed

ASKER

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.

peh803

Please see my suggestions in this related post from yesterday:

https://www.experts-exchange.com/questions/21155464/buffer.html

regards,
peh803

ASKER CERTIFIED SOLUTION

hongjun

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

peh803

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

Regards,
peh803

peh803

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

fritz_the_blank

Occassionally, you should output the buffer--

Response.flush()

FtB

fritz_the_blank

Oops, please ignore my comment as it duplicates one above.

FtB

pavelmed

ASKER

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

pavelmed

ASKER

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

hongjun

it should preferably after your @Language=...
example

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

<%
...the rest here
%>

pavelmed

ASKER

I don't have @Language=VBScript on the page since this is a default for ASP

pavelmed

ASKER

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

Thanks

peh803

@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

pavelmed

ASKER

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

peh803

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

pavelmed

ASKER

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

peh803

Glad to have helped.

Regards,
peh803