asp text file out of memory

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



"Sunshine" <sunshine--NO-SPAM-@fwc.state.fl.u s> wrote in message
news:%23LozYxNG EHA.1228@TK2MSF TNGP11.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.CreateOb ject("ADODB.Con nection")
> conn.CommandTim eout=0
> conn.Connection Timeout=0
> conn.open "dsn=dsn;"
>
> Session("Values ") = "SELECT Satement"
>
> set Recordset1 = conn.execute(Se ssion("Values") )
>
> Dim F, Head
> Head = " "
> For Each F In Recordset1.Fiel ds
> Head = Head & "," & F.Name
> Next
>
> Head = Mid(Head,3) & vbCrLf Response.Conten tType = "text/plain"
> Response.Write Head
> Response.Write Recordset1.GetS tring(,,",",vbC rLf,"")
>
>[/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@TRASHasp faq.com> wrote in message
news:OK9FbNOGEH A.3984@TK2MSFTN GP10.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.u s> wrote in message
> news:%23LozYxNG EHA.1228@TK2MSF TNGP11.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.CreateOb ject("ADODB.Con nection")
> > conn.CommandTim eout=0
> > conn.Connection Timeout=0
> > conn.open "dsn=dsn;"
> >
> > Session("Values ") = "SELECT Satement"
> >
> > set Recordset1 = conn.execute(Se ssion("Values") )
> >
> > Dim F, Head
> > Head = " "
> > For Each F In Recordset1.Fiel ds
> > Head = Head & "," & F.Name
> > Next
> >
> > Head = Mid(Head,3) & vbCrLf Response.Conten tType = "text/plain"
> > Response.Write Head
> > Response.Write Recordset1.GetS tring(,,",",vbC rLf,"")
> >
> >[/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






"Sunshine" <sunshine--NO-SPAM-@fwc.state.fl.u s> wrote in message
news:eRc4iBPGEH A.3472@TK2MSFTN GP11.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.u s> wrote in message
news:#LozYxNGEH A.1228@TK2MSFTN GP11.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.CreateOb ject("ADODB.Con nection")
> conn.CommandTim eout=0
> conn.Connection Timeout=0
> conn.open "dsn=dsn;"
>
> Session("Values ") = "SELECT Satement"
>
> set Recordset1 = conn.execute(Se ssion("Values") )
>
> Dim F, Head
> Head = " "
> For Each F In Recordset1.Fiel ds
> Head = Head & "," & F.Name
> Next
>
> Head = Mid(Head,3) & vbCrLf Response.Conten tType = "text/plain"
> Response.Write Head
> Response.Write Recordset1.GetS tring(,,",",vbC rLf,"")
>
>[/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"