Introduction to MSSQL Management Data Warehouse (MDW) presentation

Last night I presented an intro to SQL Server 2008’s MDW to the fwPASS Professional Association. The subject is primarily of interest to DBA’s rather than Developers since it is best leveraged by monitoring and managing an environment of multiple SQL Servers.

Introduction to SQL Server 2008 Management Data Warehouse (MDW)

View more presentations from Dean Willson.

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

Introduction to SSRS Report Builder

I made a short 30 minute presentation to fwPASS yesterday on the topic of Report Builder after Mark Dalman presented an Introduction to BizTalk. The intention was to present on Report Builder 3.0 (SQL Server 2008R2 version), but unfortunately I had SQL Server installation issues that prevented it. I presented using SQL Server 2005 Report Builder instead, but I highlighted some of the new features found in Version 3.0.

The first three slides are just tidbits of fwPASS information followed by the presentation.

Introduction to SSRS Report Builder

View more presentations from DeanWillson.

A side note: Other than slide 8, I created the entire presentation using Microsoft’s PowerPoint WebApp on my Windows Live SkyDrive account. It is a pretty nifty web-based alternative to Google Docs

Add Reporting Services to Existing MSSQL 2005 Install on Vista

This week I attempted to add Reporting Services to an existing SQL Server 2005 installation. I was met with an error that IIS was not configured, so the checkbox to install the Reporting Services component was greyed out. I have been using IIS on this box for over a year so the install was obviously looking for some configuration flag or feature to be installed that was not. The first thing I did was verify that the IIS 6 Compatibility options were installed, which they were. I remember adding them a year ago for something else that required it.

I happened across another blog http://www.igregor.net/post/2008/01/Installing-SQL-Server-2005-Reporting-Service-on-IIS-7.aspx which showed the many required Windows features in the Control Panel -- Programs and Features -- "Turn Windows features on or off" section. The only one that was unchecked was under the Common HTTP Features. The HTTP Redirection feature was unchecked. I checked the checkbox and that allowed me to add the Reporting Services component to my SQL Server installation.

The installation is complete. I ran through the Reporting Services configuration tool and hit the http://localhost/reports page. Oops, error. Finally with the help of http://forums.microsoft.com/TechNet/ShowPost.aspx?PostID=2834686&SiteID=17 Michael R. eluded to the removal of an extra backslash in the Handler Mapping which did resolve the issue. I don't know how he ever found that apparent typo that was configured by Reporting Services, but that solved the problem.

With the help of another handy blog, http://www.tlbignerd.com/2007/10/sql-server-2005-reporting-services-on.html, I got the properties to show up on the Reports page by temporarily turning off the UAC and adding a user with appropriate roles to the Report Services web configuration.

VS2008 ASP.Net ReportViewer rendering issues after Vista SP1 Install

Background:
I was able to successfully create ASP.Net web forms in Visual Studio 2008 with Report Viewer controls to display Reporting data before installing Vista SP1 on my development box, possibly also before installing the Visual Studio 2008 Hotfix. After installing Vista SP1, when I tried to create a report, I received either no data at all in the Report Viewer control in the browser, or an error that read:

'document.getElementById(...).Controller' is null or not an object

I don't know if this was caused by the upgrade to Vista SP1 which supposedly sych'd the Server 2008 IIS codebase with Vista IIS codebase, but the fix required adding a Managed Handler to the Handler Mappings in IIS7 as found in the link below:

http://otkfounder.blogspot.com/2007/11/solving-reportviewer-rendering-issue-on.html

This resolved the problem and my reports rendered as expected. Many thanks to Antonio Ooi of OTK Web Solutions for the fix.

Deploying VS 2008 projects using MicrosoftReportViewer

Caveats to using MicrosoftReportViewer control to create Client side Reports on a tightly controlled Production Server:

1. ReportViewer dll's are not in the .Net 3.5 Framework Redistributable. They are in a Visual Studio 2008 Runtime Redistributable http://www.microsoft.com/downloads/details.aspx?familyid=CC96C246-61E5-4D9E-BB5F-416D75A1B9EF&displaylang=en (which says it is for XP and Vista). After installing the Redist on the Server (2K3 Enterprise Ed), the report displays, but it did a fine job of locking up my Browser every time I attempted to print the report. A Messagebox alert popped up with "Unable to load client print control" and the browser displayed the yellow bar at the top of the page wanting to install a SQL Server Add-in. Sure you could probably find the .dll's (listed below) , add a reference, and set Copy Local=true (although it didn't work for me for the Microsoft.ReportViewer.ProcessingObjectModel.dll); just make sure your Production server IT support staff approves. I did not test to see if that would resolve the previously mentioned browser lockups.
   • Microsoft.ReportViewer.WinForms.dll
   • Microsoft.ReportViewer.WebForms.dll
   • Microsoft.ReportViewer.Common.dll
   • Microsoft.ReportViewer.ProcessingObjectModel.dll
2. Publishing using the VS 2008 Publish functionality does not include the necessary dll's. Just because it says "publish only files it needs to run", doesn't mean it includes all the files needed to run. Of course you don't find out you are missing some of the dll's (like the Microsoft.ReportViewer.ProcessingObjectModel.dll) until you try to display the ReportViewer report. Make sure to include that step in your verification plan.