I recently helped on an interesting problem with SQL Reporting Services, where features of SRS 2008 were just gone!
History
The team had created a number of reports in SQL Server 2005 and used them successfully for a number of years. During their upgrade to SQL Server 2008 they needed to upgrade the reports to SRS 2008 format.
To be clear here, SRS doesn’t support backwards compatibility so if you want to run a report built in 2005 on a 2008 server, it must be upgraded. Thankfully this is a simple process, just open the report solution in Business Intelligence Development Studio (BIDS) 2008 and it will upgrade it.
The Problem
The team knew this process and opened the 2005 reports in BIDS 2008, no errors were reported, all reports saved correctly and were published to the SRS server. The server then rendered them, which to everyone meant that the reports had upgraded successfully.
However there was some changes to how the reports were rendered and the team needed to change the ConsumeContainerWhitespace property on the report to true to fix that rendering issue.
This is a new property in 2008, and the problem the team had is that when the report was opened in BIDS 2008 the property just wasn’t there! They could create new reports and see it, but their existing reports did not contain it.
Diagnosis
My first thing to check was that they were opening the right version of BIDS – this is the SRS persons way of saying “Have you tried turning it off and on?”.
They verified that quickly, so my next step was to check if BIDS was actually doing the upgrade. A report is just an XML file so you can open it up in notepad and check the schema to verify what version of the report it is. If it is http://schemas.microsoft.com/sqlserver/reporting/2008/01/reportdefinition it is a 2008 report and if it is http://schemas.microsoft.com/sqlserver/reporting/2005/01/reportdefinition it is a 2005 report.
You know what, after the upgrade it was STILL showing as a 2005 report! This confused me to no end – how could SRS 2008 render a 2005 report and why was BIDS not upgrading it?
Solution
I was close to getting the team to call Microsoft, or an exorcist, for assistance – but re-reading the email conversation between the team and me a number of times I stumbled across the piece of missing information which explained the cause. The team was using .rdlc files – these are SRS files designed for client side rendering. SRS supports these and the “normal” .rdl files.
The interesting thing about .rdlc files is that they are frozen on 2005:
- So they do not get new features of 2008 or 2008 R2, which is why the new properties (like the one the team wanted) does not appear.
- The schema remains on 2005 (no upgrade in BIDS)
- and to confuse everyone, they still render on 2008 and 2008 R2!
Thankfully you can convert from .rdlc to .rdl, which means then the reports get upgraded to the 2008 or 2008 R2 formats and get all the features. The team did this and have been smiling ever since!