ssrs sql 2014 cannot render report more than 65536 of rows

motioneye
motioneye used Ask the Experts™
on
GUys,
I have sql 2014 ent edition that installed with SSRS, this version of sql servers was actually an upgrade version from sql 2008 R2. Today I have a report that having a row size more than 65536 and somewhat report subscriptions failed to sending this report with following errors.  from what I understood this sql 2014 ssrs should have bigger row limit size.


 
 An updated rendering extension for Microsoft Excel 2007–2010, supported both by Report Builder and the SSRS web rendering controls (both covered in this chapter). The maximum number of rows you can export from an SSRS report to an Excel worksheet is now 1,048,576, the maximum number of columns you can export is now 16,384, and the maximum number of colors supported for generated content is 16 million. The export format is Open Office XML (OOXML), and files are generated as zip-compressed *.xlsx archives.
 
 
 
 
 ▶
 
 An updated rendering extension for Microsoft Word 2007–2010 (and 2003). It also supports the OOXML standard, generating zip-compressed *.docx files. Note that in order to open *.docx and *.xlsx files, Microsoft Office clients must be at version 2007 or later, or you must install the Office Compatibility Pack (available free from Microsoft’s download site)


Failure sending mail: Excel Rendering Extension: Number of rows exceeds the maximum possible in a worksheet of this version. Rows requested: 105676. Maximum rows: 65536.Mail will not be resent.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Robb HillSenior .Net Full Stack Developer

Commented:
This could be a limitation with excel not ssrs.  What version of Excel?  

Try export to csv and see if that works ...should prove if its excel or not.

Author

Commented:
Hi Rob,
But the error below actually from ssrs subscription log. So how do I make ssrs subscription to be able to save  the records in excel2003 format with row size of  more than 100k  ?

Failure sending mail: Excel Rendering Extension: Number of rows exceeds the maximum possible in a worksheet of this version. Rows requested: 105676. Maximum rows: 65536.Mail will not be resent.
Jim HornSQL Server Data Dude
Most Valuable Expert 2013
Author of the Year 2015

Commented:
>Today I have a report that having a row size more than 65536
Just to state the obvious, why??  64k rows probably translates to about 10k+ pages, which is more then the average human will read in a cosmetically readable format.   If this is ultimately consumed by another process then this would better be served as an SSIS package exporting a .csv file, and if needed a SSRS summary report can be created.
Introduction to R

R is considered the predominant language for data scientist and statisticians. Learn how to use R for your own data science projects.

Chief Technology Officer
Commented:
Excel 2003 (.xls) is limited to 64565 rows.  Excel 2007-2019 (.xlsx) has the 1M+ capability.
You'll want to make sure that SSRS is using the ISAM for Excel that exports it as a 2007+ version.
Robb HillSenior .Net Full Stack Developer

Commented:
There is an easy test to this to verify my original post.

Limit the query for your dataset to less than 10k....

If that doesnt work you either will need to upgrade your Excel.  Or change your export expectations.


Please let me know how it goes.

Thanks,

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial