We help IT Professionals succeed at work.

Sql server 2008 R2 excel output reach limit help

Medium Priority
484 Views
Last Modified: 2012-06-27
I have a database that is running in windows 2008 R2, I have users  who are pulling reports and exporting them into an excel file. Each report contains 80,000 records/rows,  outputted to excel does not work as it exceeds the limit. Excel 2007/2010 can go up to over 1 million rows,  how do I get Sql server to out to a excel 2007 format?
Comment
Watch Question

Gerwin Jansen, EE MVETopic Advisor
CERTIFIED EXPERT
Most Valuable Expert 2016

Commented:
Can you describe how the users are getting the records? It's not that they put the record in a .xls file instead of .xlsx right? The last one supports more rows.

Author

Commented:
It's a visual basic front end that connects to the sql server
Gerwin Jansen, EE MVETopic Advisor
CERTIFIED EXPERT
Most Valuable Expert 2016

Commented:
Can you post details about the error that you're getting? Is it from the vb app?

For testing: can you get data from your sql server using Data->From Other Sources->From SQL Server in Excel 2007/2010 - I expect you don't get the limit problem there, right?

Commented:
We were having the same issue and were able to resolve it with a product called Aspose.Cells.  It is a reporting services add-in.  With some simple customization, we have added Excel 2007 exports to our reports and our users are thrilled.
Commented:
we found that the problem was with SSIS reporting and not an excel issue. SQL server 2008R2 has a reporting limited of 65K rows. SQL server 2012 fixes that with 1m rows

Author

Commented:
We found it after searching on the net for the longest time

Explore More ContentExplore courses, solutions, and other research materials related to this topic.