asp text file out of memory

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,"")

re: asp text file out of memory

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?

--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/


"Sunshine" <sunshine--NO-SPAM-@fwc.state.fl.us> wrote in message
news:%23LozYxNGEHA.1228@TK2MSFTNGP11.phx.gbl...[color=blue]
> 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,"")
>
>[/color]

re: asp text file out of memory

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]" <aaron@TRASHaspfaq.com> wrote in message
news:OK9FbNOGEHA.3984@TK2MSFTNGP10.phx.gbl...[color=blue]
> 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?
>
> --
> Aaron Bertrand
> SQL Server MVP
> http://www.aspfaq.com/
>
>
> "Sunshine" <sunshine--NO-SPAM-@fwc.state.fl.us> wrote in message
> news:%23LozYxNGEHA.1228@TK2MSFTNGP11.phx.gbl...[color=green]
> > 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[/color][/color]
to[color=blue][color=green]
> > 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,"")
> >
> >[/color]
>
>[/color]

re: asp text file out of memory

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.

--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/





"Sunshine" <sunshine--NO-SPAM-@fwc.state.fl.us> wrote in message
news:eRc4iBPGEHA.3472@TK2MSFTNGP11.phx.gbl...[color=blue]
> While I really appreciate you taking the time to respond, I still don't
> know
> how to do any of your suggestions.
> Sunshine[/color]

re: asp text file out of memory

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).

"Sunshine" <sunshine--NO-SPAM-@fwc.state.fl.us> wrote in message
news:#LozYxNGEHA.1228@TK2MSFTNGP11.phx.gbl...[color=blue]
> Hello all. I have a page which sends a query to an DB2 using an ODBC.[/color]
The[color=blue]
> 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[/color]
the[color=blue]
> 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,"")
>
>[/color]

re: asp text file out of memory

Sunshine wrote:[color=blue]
> While I really appreciate you taking the time to respond, I still
> don't know how to do any of your suggestions.
> Sunshine
>[/color]

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
--
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"