The following is the first tip in a series on how to troubleshoot common SQL Server 2005 Reporting Services dilemmas.
SQL Server Reporting Services 2005 can export to a variety of formats including PDF and the ubiquitously-used, comma-separated value (CSV). If you're exporting to Excel a great deal and need a good format for speed – and don't need pretty formatting – CSV is usually the best way to go.
But there's one small problem: Reporting Services exports CSV files as Unicode by default, not ASCII. This creates problems for Excel because Excel's CSV import module has difficulty automatically recognizing CSV files written in Unicode. If you attempt to import into Excel, the import process can take an agonizingly long time, and it will only get exponentially worse for bigger files. This may manifest to the end user as what seems to be a sluggish download process, when in fact the problem is Excel's file conversion.
To get around this dilemma, you can edit Internet Information Server's (IIS) rsreportserver.config file to export to ASCII .CSV by default:
1. Open the rsreportserver.config file with a text editor and look for this block:
<Extension Name="CSV" Type="Microsoft.ReportingServices.Rendering.CsvRenderer.CsvReport,Microsoft. ReportingServices.CsvRendering"/>
2. Comment out that section with <!-- and --> marks.
3. Directly below that comment section, add the following:
<Extension Name="CSV" Type="Microsoft.ReportingServices.Rendering.CsvRenderer.CsvReport, Microsoft.ReportingServices.CsvRendering"> <Configuration> <DeviceInfo> <Encoding>ASCII</Encoding> </DeviceInfo> </Configuration> </Extension>
4. Run IISRESTART to reload the configuration file.
The reason you'll want to comment out the original block rather than delete it is so that you can revert back to the original settings in the unlikely event the new ones don't work correctly.
About the author: Serdar Yegulalp is editor of the Windows Power Users Newsletter. Check it out for the latest advice and musings on the world of Windows network administrators -- and please share your thoughts as well!
More information from SearchSQLServer.com