CODEWITHSUNDEEP
mysqlasp-classicrecordset
Steve A
Steve A

Reputation: 2013

Server error based on the amount of data returned in an ASP classic ODBC query

The message, "An error occurred on the server when processing the URL..." appears when I execute an ASP Classic (ODBC) SQL query. Whether the error message is displayed depends on the amount of data that would be returned by the query. The error only occurs when I execute the query from asp and not when executed from phpMyAdmin.

I can execute this without an error:

SELECT * FROM `customers` LIMIT 500

However, this next query results in "An error occurred on the server when processing the URL. Please contact the system administrator. If you are the system administrator please click here to find out more about this error."

SELECT * FROM `customers`

The above query would return ~3200 records each of which contain 27 fields.

The "here" link in the above error message directs to "https://learn.microsoft.com/en-us/iis/application-frameworks/running-classic-asp-applications-on-iis-7-and-iis-8/classic-asp-not-installed-by-default-on-iis" which implies that ASP is not installed. It is obviously installed because I can execute the previous query which includes a LIMIT on the results.

If I trial-and-error the LIMIT in the SQL query, I discover that I can execute this:

SELECT * FROM `customers` LIMIT 702

But this next query results in the error: 

SELECT * FROM `customers` LIMIT 703

The LIMIT is related to the number of fields returned by the SQL query. For example, I can execute either of the following queries (they are almost identical, but one includes the City and one includes the Zipcode):

SELECT CustomerID, Name, Address1, Address2, City FROM `customers`
SELECT CustomerID, Name, Address1, Address2, State FROM `customers`

Both of the above queries return ~3100 records that contain 5 fields.

However, I can't execute this next query which would return 6 fields for those same ~3100 records:

SELECT CustomerID, Name, Address1, Address2, City, State FROM `customers`

It really feels like there is a limit the the number of records multiplied by the number of fields.

I am using a shared hosting service. I'm wondering if there is a setting on their end that would cause this error. I didn't encounter this with my previous host.

Thanks!

Upvotes: 0

Views: 225

Answers (1)

Stephen R
Stephen R

Reputation: 3917

ASP caches page content, and you're probably hitting the limit of the cache size. All you have to do is occasionally dump the cache. I use something like this:

x = x+1
if x mod 500 = 0 then
    Response.Flush
end if

Put that at the end of the loop that iterates each record (inside the loop). Every 500 records it will dump the cache to output, then continue.

(The other option is to increase the cache size, but I believe the above is a better option. No need to needlessly increase the load on your server.)

Upvotes: 1

Related Questions