DTS: Export to Excel: "Spreadsheet Full"

  • I have a DTS package that exports from a SP to an Excel spreadsheet.  The spreadsheet is deleted and recreated first within the same package.  These steps work perfectly.

    During the export, it runs up to ~65K records and then errors out with the following error:

    "The number of failing rows exceeds the maximum specified.  Spreadsheet is full."

    Is there a limit exporting to Excel, or is there an option I need to configure?

     

    Thanks for the help!

     

  • The maximum worksheet size in Excel is 65,536 rows by 256 columns, so it sounds like you have hit that limit. However, you can of course have multiple worksheets within a workbook.

    This is a hell of a lot of data for a spreadsheet to handle. Perhaps the time has come to consider a database solution.

    Regards

    Phil

    The absence of evidence is not evidence of absence
    - Martin Rees
    The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
    - Phil Parkin

  • I did just realized the limitation.  I created an online tool for marketing to create dyanmic lists to compare certain numbers from our database.  It worked great until the numbers got this high.  I'm switching them to Access which I tried to avoid in the first place for a few reasons.

     

    Thanks for the input.

  • You side you wanted an online tool for Marketing to create lists for comparison.

    You may want to look at using SQL Server Data Analysis (a.k.a. OLAP, Cubes) and providing the user an Excel spreadsheet with a link to the data via pivot table.  You can create a job in SQL Job Agent to automatically refresh the data at a certain time and the Excel pivot table automatically refreshes so your data is "live".  You can even create an HTML version of the Excel sheet so that all the user will need is IE.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply