asp text file out of memory

[Sunshine]

Hello all. I have a page which sends a query to an DB2 using an ODBC. The
results are then written to a text file. I am unable to write directly to
the users drive because of permissions. The problem I have is that when the
query set becomes too big, I get error '8007000e' Not enough storage is
available to complete this operation. The query is returning 100, 000
records or more. So my question is how do I handle this? Somebody had
suggested to me to chunk the data. However, I'm just a beginner and I
couldn't find an example on the web. I would appreciate any help...

Here is my code:
set conn = Server.CreateObject("ADODB.Connection")
conn.CommandTimeout=0
conn.ConnectionTimeout=0
conn.open "dsn=dsn;"

Session("Values") = "SELECT Satement"

set Recordset1 = conn.execute(Session("Values"))

Dim F, Head
Head = " "
For Each F In Recordset1.Fields
Head = Head & "," & F.Name
Next

Head = Mid(Head,3) & vbCrLf Response.ContentType = "text/plain"
Response.Write Head
Response.Write Recordset1.GetString(,,",",vbCrLf,"")

 

[Aaron Bertrand [MVP]]

Uh, chunk the query out into sections instead of trying to write the whole
table out in one shot? Or use DB2's facilities to write the file out,
instead of bringing all that crap into ASP's memory/buffer?

 

[Sunshine]

While I really appreciate you taking the time to respond, I still don't know
how to do any of your suggestions.
Sunshine

 

[Aaron Bertrand [MVP]]

DB2 has an EXPORT command, so you could call something like this from ASP,
so that ASP doesn't have to deal with all of the text, it just has to wait
for the export to complete.

db2 export file_name.txt of del SELECT * FROM table_name

Look under DB2 help for more information on the export command. Your
ability to execute this from ASP may depend on the driver you are using to
connect and/or your user's permissions on the target machine of the text
file.

 

[J. Baute]

Try looping your recordset with a Do While and write each record line by
line to avoid ADO having to return the complete recordset in one string
(GetString).

 

[Bob Barrows]

While I really appreciate you taking the time to respond, I still
don't know how to do any of your suggestions.
Sunshine

He is talking about modifying your SELECT statement by adding a WHERE clause
to limit the records returned by your query.

I cannot get specific without knowing the details about your table
structure, but the idea is to do this:

Run a Select that retrieves the first 10000 rows
Use getstring to write the data to the file
Retrieve next 10000 row
write the data
repeat until finished

Bob Barrows