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

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.

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

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-us/ado270/htm/mdprocomm 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.

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

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"

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

On May 19, 9:41 am, "Bob Barrows [MVP]"
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.

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

LetMeDoIt wrote:
> On May 19, 9:41 am, "Bob Barrows [MVP]"
> 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.