Velocity Reviews - Computer Hardware Reviews

Velocity Reviews > Newsgroups > Programming > ASP .Net > ASP General > Large row count from stored procedure

Reply
Thread Tools

Large row count from stored procedure

 
 
Dooza
Guest
Posts: n/a
 
      06-25-2009

Hi there,
I am using an ASP page to output to Excel file. Its using this at the
top of the page:

<%
Change HTML header to specify Excel's MIME content type
Response.Buffer = TRUE
Response.ContentType = "application/vnd.ms-excel"
Response.Addheader "Content-Disposition",
"attachment;Filename=Export.xls"
%>

I am using an ADODB command to access a stored procedure in SQL 2000.
This stored procedure takes 5 inputs, and returns 2 recordsets.

The first recordset is always just 1 row, and is displayed in a table.

The second recordset can vary between a 10 rows and 35,000 rows.

On smaller rows it works just fine, but with large numbers of rows the
object is destroyed before it gets displayed. I am assuming its down to
the size, or the server giving up, but it doesn't time out which is what
I would expect to happen.

Is there a limit, or is it a resources issue? Can I set the limit if
there is one? Or is there a better way to do this?

Cheers,

Steve
 
Reply With Quote
 
 
 
 
Bob Barrows
Guest
Posts: n/a
 
      06-25-2009

Dooza wrote:
> Hi there,
> I am using an ASP page to output to Excel file. Its using this at the
> top of the page:
>
> <%
> Change HTML header to specify Excel's MIME content type
> Response.Buffer = TRUE
> Response.ContentType = "application/vnd.ms-excel"
> Response.Addheader "Content-Disposition",
> "attachment;Filename=Export.xls"
> %>
>
> I am using an ADODB command to access a stored procedure in SQL 2000.
> This stored procedure takes 5 inputs, and returns 2 recordsets.
>
> The first recordset is always just 1 row, and is displayed in a table.
>
> The second recordset can vary between a 10 rows and 35,000 rows.


35000?!?!?
Err ... did you consider paging these records ... ?

>
> On smaller rows it works just fine, but with large numbers of rows the
> object is destroyed before it gets displayed. I am assuming its down
> to the size, or the server giving up, but it doesn't time out which
> is what I would expect to happen.
>
> Is there a limit, or is it a resources issue? Can I set the limit if
> there is one? Or is there a better way to do this?
>

Paging immediately suggests itself to me. Look it up at www.aspfaq.com

--
HTH,
Bob Barrows


 
Reply With Quote
 
 
 
 
Dooza
Guest
Posts: n/a
 
      06-25-2009

Bob Barrows wrote:
> Dooza wrote:
>> Hi there,
>> I am using an ASP page to output to Excel file. Its using this at the
>> top of the page:
>>
>> <%
>> Change HTML header to specify Excel's MIME content type
>> Response.Buffer = TRUE
>> Response.ContentType = "application/vnd.ms-excel"
>> Response.Addheader "Content-Disposition",
>> "attachment;Filename=Export.xls"
>> %>
>>
>> I am using an ADODB command to access a stored procedure in SQL 2000.
>> This stored procedure takes 5 inputs, and returns 2 recordsets.
>>
>> The first recordset is always just 1 row, and is displayed in a table.
>>
>> The second recordset can vary between a 10 rows and 35,000 rows.

>
> 35000?!?!?
> Err ... did you consider paging these records ... ?
>
>> On smaller rows it works just fine, but with large numbers of rows the
>> object is destroyed before it gets displayed. I am assuming its down
>> to the size, or the server giving up, but it doesn't time out which
>> is what I would expect to happen.
>>
>> Is there a limit, or is it a resources issue? Can I set the limit if
>> there is one? Or is there a better way to do this?
>>

> Paging immediately suggests itself to me. Look it up at www.aspfaq.com


I dont know if paging would work when dumping it to excel, or maybe it
would, I dont know.

Currently there is a form, you chose various options, submit, and output
is via excel, as that is the format I have been asked to make the report
in. Excel can take over 65000 rows. But I guess ADODB must have a limit,
but I can't find any information about this limit.

I was thinking about maybe output XML from the stored procedure, and
streaming the output, what do you think? Would that work? Can I get that
in excel?

Steve
 
Reply With Quote
 
Daniel Crichton
Guest
Posts: n/a
 
      06-25-2009

Dooza wrote on Thu, 25 Jun 2009 12:20:59 +0100:

> Hi there,
> I am using an ASP page to output to Excel file. Its using this at the top
> of the page:


> <%
> Change HTML header to specify Excel's MIME content type
> Response.Buffer = TRUE
> Response.ContentType = "application/vnd.ms-excel"
> Response.Addheader "Content-Disposition",
> "attachment;Filename=Export.xls"
> %>


> I am using an ADODB command to access a stored procedure in SQL 2000. This
> stored procedure takes 5 inputs, and returns 2 recordsets.


> The first recordset is always just 1 row, and is displayed in a table.


> The second recordset can vary between a 10 rows and 35,000 rows.


> On smaller rows it works just fine, but with large numbers of rows the
> object is destroyed before it gets displayed. I am assuming its down to
> the size, or the server giving up, but it doesn't time out which is
> what I would expect to happen.


> Is there a limit, or is it a resources issue? Can I set the limit if there
> is one? Or is there a better way to do this?


> Cheers,


> Steve


Use

Response.Buffer = False

and also in your loop that is writing the data out add a

Response.Flush

every 100 rows or so. I do both of these because I've found that even with
the buffer set to false that there is some buffering going on, and the flush
helps stop this causing the ASP engine from throwing an error due to the
buffer filling up.

--
Dan


 
Reply With Quote
 
Dooza
Guest
Posts: n/a
 
      06-25-2009

Daniel Crichton wrote:
> Response.Buffer = False
>
> and also in your loop that is writing the data out add a
>
> Response.Flush
>
> every 100 rows or so. I do both of these because I've found that even with
> the buffer set to false that there is some buffering going on, and the flush
> helps stop this causing the ASP engine from throwing an error due to the
> buffer filling up.



Hi Daniel,
I have tried as you suggested, but it doesn't get passed the start of my
loop:

Microsoft VBScript runtime error '800a01a8'

Object required

/spreadsheet/excel.asp, line 140

Line 140 is <% Do While NOT rsData.EOF %>

With a smaller dataset I am informed that buffering must be on when I
try to do the flush.

Steve
 
Reply With Quote
 
Bob Barrows
Guest
Posts: n/a
 
      06-25-2009

Dooza wrote:
> Daniel Crichton wrote:
>> Response.Buffer = False
>>
>> and also in your loop that is writing the data out add a
>>
>> Response.Flush
>>
>> every 100 rows or so. I do both of these because I've found that
>> even with the buffer set to false that there is some buffering going
>> on, and the flush helps stop this causing the ASP engine from
>> throwing an error due to the buffer filling up.

>
>
> Hi Daniel,
> I have tried as you suggested, but it doesn't get passed the start of
> my loop:
>
> Microsoft VBScript runtime error '800a01a8'
>
> Object required
>
> /spreadsheet/excel.asp, line 140
>
> Line 140 is <% Do While NOT rsData.EOF %>
>
> With a smaller dataset I am informed that buffering must be on when I
> try to do the flush.
>

Can you successfully use getrows to pull the data into an array as a
test?

--
HTH,
Bob Barrows


 
Reply With Quote
 
Bob Barrows
Guest
Posts: n/a
 
      06-25-2009

Dooza wrote:
>
> I dont know if paging would work when dumping it to excel, or maybe it
> would, I dont know.
>

Oh yeah ... <blush>

Hmm, is it possible to use DTS/SSIS to export this data to a spreadsheet
instead? You can then stream it to the user. I guess the answer would
depend on whether this was for an intranet or for the internet.
--
HTH,
Bob Barrows


 
Reply With Quote
 
Dooza
Guest
Posts: n/a
 
      06-25-2009

Bob Barrows wrote:
> Dooza wrote:
>> I dont know if paging would work when dumping it to excel, or maybe it
>> would, I dont know.
>>

> Oh yeah ... <blush>
>
> Hmm, is it possible to use DTS/SSIS to export this data to a spreadsheet
> instead? You can then stream it to the user. I guess the answer would
> depend on whether this was for an intranet or for the internet.


Its an intranet, but needs data from the user first.

Its a sales report. You can either give a string of item codes, comma
separated, or select a manufacturer from a drop down list.

You then enter the freight and duty multipliers, and optionally select a
country from another drop down list.

Steve
 
Reply With Quote
 
Dooza
Guest
Posts: n/a
 
      06-25-2009

Bob Barrows wrote:
> Dooza wrote:
>> Daniel Crichton wrote:
>>> Response.Buffer = False
>>>
>>> and also in your loop that is writing the data out add a
>>>
>>> Response.Flush
>>>
>>> every 100 rows or so. I do both of these because I've found that
>>> even with the buffer set to false that there is some buffering going
>>> on, and the flush helps stop this causing the ASP engine from
>>> throwing an error due to the buffer filling up.

>>
>> Hi Daniel,
>> I have tried as you suggested, but it doesn't get passed the start of
>> my loop:
>>
>> Microsoft VBScript runtime error '800a01a8'
>>
>> Object required
>>
>> /spreadsheet/excel.asp, line 140
>>
>> Line 140 is <% Do While NOT rsData.EOF %>
>>
>> With a smaller dataset I am informed that buffering must be on when I
>> try to do the flush.
>>

> Can you successfully use getrows to pull the data into an array as a
> test?


I decided to change my stored procedure to output just one recordset,
and then to use getrows, but I get the same error message, just now it
points to the getrows line.

Steve
 
Reply With Quote
 
Dooza
Guest
Posts: n/a
 
      06-25-2009

Dooza wrote:
> Bob Barrows wrote:
>> Dooza wrote:
>>> Daniel Crichton wrote:
>>>> Response.Buffer = False
>>>>
>>>> and also in your loop that is writing the data out add a
>>>>
>>>> Response.Flush
>>>>
>>>> every 100 rows or so. I do both of these because I've found that
>>>> even with the buffer set to false that there is some buffering going
>>>> on, and the flush helps stop this causing the ASP engine from
>>>> throwing an error due to the buffer filling up.
>>>
>>> Hi Daniel,
>>> I have tried as you suggested, but it doesn't get passed the start of
>>> my loop:
>>>
>>> Microsoft VBScript runtime error '800a01a8'
>>>
>>> Object required
>>>
>>> /spreadsheet/excel.asp, line 140
>>>
>>> Line 140 is <% Do While NOT rsData.EOF %>
>>>
>>> With a smaller dataset I am informed that buffering must be on when I
>>> try to do the flush.
>>>

>> Can you successfully use getrows to pull the data into an array as a
>> test?

>
> I decided to change my stored procedure to output just one recordset,
> and then to use getrows, but I get the same error message, just now it
> points to the getrows line.
>
> Steve


Just noticed I am using a DSN on the server, is this perhaps limited the
rows returned?

Steve
 
Reply With Quote
 
 
 
Reply

Thread Tools

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
procedure as argument in procedure AlexWare VHDL 2 10-23-2009 09:14 AM
'Procedure or function <stored procedure name> has too many arguments specified',,,ARGH! Mike P ASP .Net 0 06-19-2006 01:19 PM
Gridview - how to delete row using stored procedure? dotnw@hotmail.com ASP .Net Datagrid Control 1 11-21-2005 10:35 PM
ok I can do a totals row but how about a percentage row after each data row D ASP .Net Datagrid Control 0 05-23-2005 04:10 PM
I am adding a new row to the datagrid dynamically but if i use the Count property of Item it is not showing the count of the new rows being added Praveen Balanagendra via .NET 247 ASP .Net 2 06-06-2004 07:16 AM



Advertisments