January 2, 2009 at 5:25 am
I have posted this on the MSDN forums for Reporting services but have not had any replies.
Hi,
We are using SQL RS 2005 to try and replace Crystal Business Objects.
We are also trying to speed up some processes that take time on certain days of the week.
We schedule our reports to output an excel file into a certain folder.
Whenever one of our new excel 2007 files (with linkage to the report files) tries to open these excel report files a message box comes up saying 'Unable to read file', if we open and save the report file the filesize increases by about 10kb and the error goes away.
We are using Excel 2007 to host the main excel file.
We are running SQL 2005 Standard SP2
Is excel output from SQL RS broken slightly? Has there been a fix since SP2?
Someone replied saying that I should try to create a blank 2003 file and see if that has errors when linked into the main Excel file. The answer was no.
If I create a 2003 file from Excel 2007 (the only version of Excel we have) then there is no problem opening it.
Further info on the problem. The reports I run spit out 6-10kb files in xls format (2003). Opening and saving them in Excel 2007 makes them jump to 16-21kb each. If I create a blank xls (2003 format) file in Excel 2007 then it is 16kb in size.
Examining the files using Notepad I find that the Excel output files from RS do not have a block at the bottom of the file that tells us which version of Office the file is from.
For example:
þÿ à…ŸòùOh«‘ +'³Ù0 @ H X h €
Œ ˜ ä Stuart Stuart Microsoft Excel @ OžÚ¨iÉ@ €ï c©iÉ þÿ ÕÍÕœ.“— +,ù®0 Ø P X d l t | „
Œ µ ä Sheet1 Sheet2 Sheet3 Worksheets þÿ
ÿÿÿÿ À F& Microsoft Office Excel 2003 Worksheet Biff8 Excel.Sheet.8 ô9²q C o m p O b j ÿÿÿÿÿÿÿÿÿÿÿÿ r ÿÿÿÿÿÿÿÿÿÿÿÿ ÿÿÿÿÿÿÿÿÿÿÿÿ ÿÿÿÿÿÿÿÿÿÿÿÿ
The above is missing from a RS 2005 exported Excel file.
Could the DLL's that allow the excel output from RS 2005 be broken on our install?
Could someone check if they are getting the same issues?
Stuart
January 3, 2009 at 7:29 am
So you don't receive the error every time correct? Just on certain days? What is the difference in the output? Is there more data where you would be running into a row restriction or columns possibly? I did find one posting in regards to a column support issue with SSRS that might be relevant - http://support.microsoft.com/default.aspx/kb/888531. The ouput for Excel is going to be the 2000/2003 format, so you are going to run into different file size and limit restrictions that you don't have in Excel 2007.
There must be something different in the output though if you don't get this error each time. Can you run the report for a day that failed through report manager and output the file to excel without any issues?
----------------------------------------------------------------------------------------
Dan English - http://denglishbi.wordpress.com
January 5, 2009 at 1:42 am
Sorry about being vague in the earlier post. The error happens every time but the process only needs to happen on certain days of the week.
The problem is with the generated files.
January 6, 2009 at 5:30 am
We are using SQL version 9.0.3073.
I notice that in CU10 the excel rendering DLL has the version 9.0.3294.0. Could this fix the issue we are having or was there no real change to this file?
January 6, 2009 at 5:47 am
Have you tried a different report and exporting to Excel? If it is something specific to a particular report then you must be running into a limitation with the engine.
Not sure if there was anything specific to the rendering engine that was updated. I know in SSRS 2008 they have fixed the issue with embedded data region and now support that.
If you are thinking about CU10 then you might as well go with SP3 and get up-to-date on your service pack.
You can refer to the build list[/url] and go through the KB articles to see if you can find anything specific.
----------------------------------------------------------------------------------------
Dan English - http://denglishbi.wordpress.com
January 12, 2009 at 3:12 am
Having checked output from Crystal against the problems we were having from RS it seems it could actually be Excel 2007's handling of xls(97-2003) format files. Both Crystal and RS output the same file structure, so it does not seem to be an RS issue.
January 13, 2009 at 7:24 am
I'm wondering why, with SQL 2005 available, you're still using a solution that involves linking spreadsheets together. Generally, if you need to link spreadsheets, then you probably actually need a good relational database holding the data instead. Linked spreadsheets was a band-aid from back in the day when the limitations of individual spreadsheets were putting a serious kai-bosh on getting more advanced work done. Of course, even back then, the right solution was far more likely a relational database.
Just my two cents worth...
Steve
(aka smunson)
:):):)
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
January 13, 2009 at 7:29 am
Fully aware of that and I honestly do want to update everything to a proper Relational Database system but at this time the implications are just far too great and I am just the developer that has been brought in to make gradual improvements to things. If/When I am given the opportunity to re-develop the entire system here then the Relational Database will be on the cards, but for now it has to be the way it has been done before. It still saves a lot of time and effort anyway.
January 13, 2009 at 8:36 am
Stuart,
Been there, done that. I understand completely.
Steve
(aka smunson)
:):):)
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
January 13, 2009 at 8:47 am
Although this pertains specifically to SSIS might it also be a part of your problem?
Office 2007 files have a new format and therefore are not supported by the Jet 4.0 OLE DB provider. Specifically, the Microsoft Excel data source and data destination, which uses the underlying Excel connetion manager based on JET, cannot be used with Microsoft Excel 2007 files. Microsoft Office 2007 shipped with a new OLE DB provider, the Office 12 Microsoft Access Engine OLE DB Provider (aka ACE provider). The ACE provider must be used with Microsoft Excel 2007 and Microsoft Access 2007 data files, but ACE supports only Microsoft Office 2007 data. SSIS has not been specifically tested with other data sources that is compatible with ACE, such as Microsoft Sharepoint files, though it is possible to use them with ACE.
You might want to read this as it appears to be generalized for all Office 2007 applications:
From the above url:
This download will install a set of components that can be used by non-Microsoft Office applications to read data from and write data to 2007 Office system files such as Microsoft Office Access 2007 (mdb and accdb) files and Microsoft Office Excel 2007 (xls, xlsx, and xlsb) files. Connectivity to text files is also supported.
ODBC and OLEDB drivers are installed for application developers to use in developing their applications with connectivity to Office file formats.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply