ASP code bombs out after SQL retrieve with over thousands of records

[LetMeDoIt]

Greetings,

I'm using ASP to retrieve from MSSQL and I then populate a table.
After several months of successfull retrieves, this same code now
bombs out. It turns out that if I clear out from SQL about 10,000
records, the retrieve is then successfull.

Reading some of the posts, I believe I need to set the cache. If
anyone can point out where that cache, it would be greatly
appreciated...

regards, letmedoit.

[Dave Anderson]

LetMeDoIt wrote:

I'm using ASP to retrieve from MSSQL and I then populate a
table. After several months of successfull retrieves, this
same code now bombs out. It turns out that if I clear out
from SQL about 10,000 records, the retrieve is then
successfull.

What does "clear out from SQL" mean? DELETE from the database? For that
matter, what does "bombs out" mean? You really should be specific if you
want useful responses.

Lacking your "bombs out" error message, I am guessing that ADO is timing out
on your query. In that case, you might want to bump up your CommandTimeout:

http://msdn.microsoft.com/library/en...andtimeout.asp

Reading some of the posts, I believe I need to set the cache.
If anyone can point out where that cache, it would be greatly
appreciated...

Which cache would that be? The browser's? The global assembly cache?
Neither, obviously, but maybe you will see my point. GIGO.


--
Dave Anderson

Unsolicited commercial email will be read at a cost of $500 per message. Use
of this email address implies consent to these terms.

[Bob Barrows [MVP]]

LetMeDoIt wrote:

Greetings,

I'm using ASP to retrieve from MSSQL and I then populate a table.
After several months of successfull retrieves, this same code now
bombs out. It turns out that if I clear out from SQL about 10,000
records, the retrieve is then successfull.

Reading some of the posts, I believe I need to set the cache. If
anyone can point out where that cache, it would be greatly
appreciated...

Just posting to echo Dave's comments, and to add:
Why are you retrieveing ALL the records in your database table? You should
be using a WHERE clause to limit the number of records retrieved. What user
is going to want to browse through thousands of records? At the very least,
consider using a paging solution such as described here:
http://www.aspfaq.com/show.asp?id=2120

You might also wish to consider generating a summary of the records and
displaying that to the users instead of the mass of details.

--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

[LetMeDoIt]

On May 19, 9:41 am, "Bob Barrows [MVP]" <[e-mail]>
wrote:

LetMeDoIt wrote:

Greetings,

I'm usingASPto retrieve from MSSQL and I then populate a table.
After several months of successfull retrieves, this samecodenow
bombsout. It turns out that if I clear out from SQL about 10,000
records, the retrieve is then successfull.

Reading some of the posts, I believe I need to set the cache. If
anyone can point out where that cache, it would be greatly
appreciated...

Just posting to echo Dave's comments, and to add:
Why are you retrieveing ALL the records in your database table? You should
be using a WHERE clause to limit the number of records retrieved. What user
is going to want to browse through thousands of records? At the very least,
consider using a paging solution such as described here:http://www.aspfaq.com/show.asp?id=2120

You might also wish to consider generating a summary of the records and
displaying that to the users instead of the mass of details.

--
Microsoft MVP -ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

My apologies for the cryptic paragraph. Thanks for the responses.

To answer a few questions that arose earlier:
1- There is a WHERE clause for the users, which when used, works
well. The users enter a specific string, and upon selecting the
RETRIEVE button, records are returned and put in columns and rows.
This works as expected.
2- By clearing out records from the SQL database, I mean, I actually
delete these records by typing the SQL command "delete from mytable
where..."
3- There are instances where a few users may want to see all records,
that is why that option is there. Currently, typing % and selecting
RETRIEVE from a button fetches all records
4- What I mean by "Bombing out" is that when all records are retrieved
(ie, when % is entered), instead of each records going to each row/
column within the table I created, these records are 'dumped' into my
html page (as in there are no rows and columns, thus no carriage
returns and line feed, each records continues from the same row from
the previous record, etc)

Regards.

[Bob Barrows [MVP]]

LetMeDoIt wrote:

On May 19, 9:41 am, "Bob Barrows [MVP]" <[e-mail]>
wrote:

>LetMeDoIt wrote:

>>Greetings,

>>I'm usingASPto retrieve from MSSQL and I then populate a table.
After several months of successfull retrieves, this samecodenow
bombsout. It turns out that if I clear out from SQL about 10,000
records, the retrieve is then successfull.

>>Reading some of the posts, I believe I need to set the cache. If
anyone can point out where that cache, it would be greatly
appreciated...

Just posting to echo Dave's comments, and to add:
Why are you retrieveing ALL the records in your database table? You
should
be using a WHERE clause to limit the number of records retrieved.
What user
is going to want to browse through thousands of records? At the very
least,
consider using a paging solution such as described
here:http://www.aspfaq.com/show.asp?id=2120

You might also wish to consider generating a summary of the records
and
displaying that to the users instead of the mass of details.

--
Microsoft MVP -ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so
I
don't check it very often. If you must reply off-line, then remove
the "NO SPAM"

My apologies for the cryptic paragraph. Thanks for the responses.

To answer a few questions that arose earlier:
1- There is a WHERE clause for the users, which when used, works
well. The users enter a specific string, and upon selecting the
RETRIEVE button, records are returned and put in columns and rows.
This works as expected.
2- By clearing out records from the SQL database, I mean, I actually
delete these records by typing the SQL command "delete from mytable
where..."
3- There are instances where a few users may want to see all records,
that is why that option is there. Currently, typing % and selecting
RETRIEVE from a button fetches all records
4- What I mean by "Bombing out" is that when all records are retrieved
(ie, when % is entered), instead of each records going to each row/
column within the table I created, these records are 'dumped' into my
html page (as in there are no rows and columns, thus no carriage
returns and line feed, each records continues from the same row from
the previous record, etc)

Regards.

By deleting records, you are effectively limiting the number of records
the user can retrieve. My suggestion would be to make this explicit by
using the TOP n keyword in your query. Combined with a paging solution,
you should avoid the problem described above.

Without knowing how to reproduce your symptoms, we have no chance of
being able to tell you what the problem is. It is probably related to
the technique you are using to extract the data values from the
recordset and write them to response.

--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.