ReportViewer Woes with Tight Integration between Visual Studio and SQL Server and No Backward Compatibility

Lately I find myself creating some quick client reports (ProcessingMode=local) using the Visual Studio 2010 ReportViewer control and the Reporting Services Tablix control. The issue is that my customer is on SQL Server 2000, not 2008 as is officially supported by Visual Studio 2010 and the rdlc Reporting Build Provider. How do I know?

Mainly because of the following compile error:

“The report definition is not valid.  Details: The report definition has an invalid target namespace 'http://schemas.microsoft.com/sqlserver/reporting/2008/01/reportdefinition' which cannot be upgraded.”   

The lack of backward compatibility was not immediately obvious, but I still wanted the ability to use the newer Tablix functionality in the VS 2010 IDE and have the out-of-the-box export functionality of the Reporting Services client reports. I’m not a fan of workarounds as I have written about before, but occasionally under special circumstances with the pros and cons heavily considered, I entertain the idea. In this case, a SQL 2008 upgrade is on the schedule in the next few months.

Workaround for Visual Studio 2010

Visual Studio 2010 cannot use any of the SQL Datasource Wizards or any other database related wizards with a pre-2005 database, which is disappointing for anyone developing “big corporate” applications as they are often late adopters. To work around this limitation in development, I backed up my SQL Server 2000 database and restored it as a SQL Server 2005 database so I could use the IDE for the Create Report wizard. Obviously, prior to deploying to production SQL 2000 servers, the database DDL and DML changes need to be scripted back out (in SQL 80 compatibility mode) and tested against SQL 2000 instances. Please don’t skip this step!

Workaround for SQL Server 2000 RDLC format

This is my least favorite part of the work around because it can be easy to forget to undue before deployment.

The application I am working with is a website project, not a web application. The compile error occurs when I try to “Build” the project from the IDE “Build” menu. If instead, I just navigate to the web page the report is hosted in (hosting in IIS, not the IDE integrated web server), the page and its report render correctly with no errors. In order to use the IDE’s build functionality, I need to “exclude” the webpage and rdlc file it is using while working on other pages of the project.

To exclude a file from the project, right click the filename from the Solution Explorer window and click “Exclude from Project”. Excluding a file in VS 2010 renames the file to [filename].[ext].exclude so you may get a Visual Source Safe or Team Foundation prompt if your files are under source control (recommended).

When I am ready to deploy or test the pages with ReportViewer controls, it is necessary to un-“exclude” the report aspx pages and .rdlc report definitions.

photo credit: Andy Welsh / CC BY 2.0