Large row count from stored procedure

Discussion in 'ASP General' started by Dooza, Jun 25, 2009.

  1. Dooza

    Dooza Guest

    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
     
    Dooza, Jun 25, 2009
    #1
    1. Advertising

  2. Dooza

    Bob Barrows Guest

    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
     
    Bob Barrows, Jun 25, 2009
    #2
    1. Advertising

  3. Dooza

    Dooza Guest

    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
     
    Dooza, Jun 25, 2009
    #3
  4. 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
     
    Daniel Crichton, Jun 25, 2009
    #4
  5. Dooza

    Dooza Guest

    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
     
    Dooza, Jun 25, 2009
    #5
  6. Dooza

    Bob Barrows Guest

    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
     
    Bob Barrows, Jun 25, 2009
    #6
  7. Dooza

    Bob Barrows Guest

    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
     
    Bob Barrows, Jun 25, 2009
    #7
  8. Dooza

    Dooza Guest

    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
     
    Dooza, Jun 25, 2009
    #8
  9. Dooza

    Dooza Guest

    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
     
    Dooza, Jun 25, 2009
    #9
  10. Dooza

    Dooza Guest

    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
     
    Dooza, Jun 25, 2009
    #10
  11. Dooza

    Bob Barrows Guest

    Dooza wrote:
    > 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.
    >
    >

    That does not invalidate using a package. The learning curve may be
    steep, but it's the correct technology to use for this.

    --
    HTH,
    Bob Barrows
     
    Bob Barrows, Jun 25, 2009
    #11
  12. Dooza

    Bob Barrows Guest

    Dooza wrote:
    >>
    >> 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.


    This is the first mention I've heard of an error message. What is the
    error message?
    >
    > Just noticed I am using a DSN on the server, is this perhaps limited
    > the rows returned?
    >

    Probably not, but you should jettison the ODBC connection.

    --
    HTH,
    Bob Barrows
     
    Bob Barrows, Jun 25, 2009
    #12
  13. Dooza

    Dooza Guest

    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


    I was wrong, it does work, well, I used rsDate instead of rsData. I got
    rid of the getrows and its working... slowly, but its working.

    So the question is, why did it not like having a big second recordset? I
    was using the first one as totals along the top as that was where I was
    asked to put it. Looks like I can't do it that way.

    Steve
     
    Dooza, Jun 25, 2009
    #13
  14. Dooza

    Dooza Guest

    Bob Barrows wrote:
    > Dooza wrote:
    >>> 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.

    >
    > This is the first mention I've heard of an error message. What is the
    > error message?


    Check a couple messages up, it was even in the quoted reply. Basically
    object required error.

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

    > Probably not, but you should jettison the ODBC connection.


    I agree, not sure why it was using it in the first place.

    Steve
     
    Dooza, Jun 25, 2009
    #14
  15. Dooza

    Dooza Guest

    Bob Barrows wrote:
    > Dooza wrote:
    >> 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.
    >>
    >>

    > That does not invalidate using a package. The learning curve may be
    > steep, but it's the correct technology to use for this.


    DTS may be a better solution for this, I will have a closer look and see
    if I can schedule some research in.

    Steve
     
    Dooza, Jun 25, 2009
    #15
  16. Dooza

    Dooza Guest


    > I was wrong, it does work, well, I used rsDate instead of rsData. I got
    > rid of the getrows and its working... slowly, but its working.
    >
    > So the question is, why did it not like having a big second recordset? I
    > was using the first one as totals along the top as that was where I was
    > asked to put it. Looks like I can't do it that way.


    I think I will try and have another recordset with the totals, rather
    than trying to squeeze them both from the same stored procedure.

    Steve
     
    Dooza, Jun 25, 2009
    #16
  17. Gazing into my crystal ball I observed Dooza <>
    writing in news::

    >
    >> I was wrong, it does work, well, I used rsDate instead of rsData. I
    >> got rid of the getrows and its working... slowly, but its working.
    >>
    >> So the question is, why did it not like having a big second
    >> recordset? I was using the first one as totals along the top as that
    >> was where I was asked to put it. Looks like I can't do it that way.

    >
    > I think I will try and have another recordset with the totals, rather
    > than trying to squeeze them both from the same stored procedure.
    >
    > Steve


    I would keep on trying the getrows method. Much better to put the data
    into an array, close the recordset, and loop through the array.

    --
    Adrienne Boswell at Home
    Arbpen Web Site Design Services
    http://www.cavalcade-of-coding.info
    Please respond to the group so others can share
     
    Adrienne Boswell, Jun 25, 2009
    #17
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Mike P
    Replies:
    0
    Views:
    3,313
    Mike P
    Jun 19, 2006
  2. AlexWare
    Replies:
    2
    Views:
    764
    Paul Uiterlinden
    Oct 23, 2009
  3. efelnavarro09
    Replies:
    2
    Views:
    945
    efelnavarro09
    Jan 26, 2011
  4. D
    Replies:
    0
    Views:
    221
  5. Replies:
    1
    Views:
    350
Loading...

Share This Page