Whither SQL Server Reporting Services?

Over the past several years, I've sometimes needed to run reports on a SQL Database. Given the ease with which reports can be created in Access, I figured that there had to be some reporting capabilities built into SQL Server. After some research, I was very surprised to learn that this wasn't true.

There were two obvious alternatives: stick with the free version of Crystal Reports that comes with Visual Studio (which is limited) or buy a commercial reporting tool. The old R&R Report Writer from dBase days was inexpensive and effective. Suffice to say that reporting tools today are dramatically more expensive.

Months later I learned about something called SQL Server Reporting Services. The only problem was finding it. Have you seen how many versions of SQL Server there are out there? MSDE, Express, Workgroup, Standard, Developer, Enterprise... Then you have to sort through Add-Ons such as Analysis Services, Reporting Services and Integration Services. Then you have to figure out what combinations of add-ons can be used with which versions of SQL Server. Finally, you have to figure out what obscure file to download to enable each of the various add-ons.

And did I mention the difficulty in sorting out the licensing rights to the various versions for us in development (non-production) environments?

And, to top it all of, it'll be a cold day in hell before you find any of this in Microsoft documentation. It was actually easier to find some of the information in Wikipedia than in Microsoft's docs!

So here I am, running Visual Studio 2008 on Windows Vista. Of course, VS2008 doesn't ship with SQL Server, you have to install SQL Server Express 2005. No problem there, but SQL Server Express 2005 doesn't ship with Reporting Services and you won't find any download labeled Reporting Services.

I thought I had struck gold when I found an article titled Introducing Business Intelligence Development Studio. That article says that Visual Studio 2005 has specific project types for creating reports and that you just need to select Add New Project. Fantastic! Of course, when I looked in VS2005 there was no such project type and the article had no troubleshooting tips.

Are you detecting a trend here?

I found a post where numerous people attempted to figure out how to get through this morass. The final conclusion was that you had to uninstall the Workstation components and install a new copy from a fresh download of the "SQL Express Toolkit." Of course, the person didn't post the link to that download and it isn't on MSDN Downloads.

I finally found it under the name Microsoft SQL Server 2005 Express Edition Toolkit. When I tried to install it on Vista I got a "compatibility warning." It remains to be seen what problems that causes. (Note that the Business Intelligence components under Workstation are not selected by default, so you have to make sure you check them manually.)

At this point I opened VS2008, tried to add a new project, and discovered that Business Intelligence Project was not one of the choices. (And yes, I did restart VS2008.) More digging on the web uncovered this little gem: Visual Studio 2008 Will NOT Support SQL Server 2005 Reporting Services projects.

So I started VS2005 and saw that Business Intelligence Projects was now visible at the top of the list in the Add New Project dialog in VS2005. Under that project type were two entries for Report Server Project. I also found the SQL Server Business Intelligence Development Studio (BIDS) in my Start menu under SQL Server 2005. I'm not yet sure exactly what the difference is between BIDS and the VS2005 projects.

If you need to install something other than Reporting Services, the instructions above won't help you much. After Setup is complete, you'll see this message:

The version of Business Intelligence Development Tools that is included in SQL Server 2005 Express Edition Toolkit does not include projects for SQL Server 2005 Integration Services or SQL Server 2005 Analysis Services. These projects are available only with editions of SQL Server 2005 that include Integration Services and Analysis Services. SQL Server 2005 Express Edition does not include Integration Services or Analysis Services.

You'll notice that this message does not tell you which versions of SQL Server will do what you need.

So I've gotten further than I was, but I still have several questions to resolve:

1. What is the relationship between BIDS and VS2005?
2. How do I install Analysis Services?
3. How do I install the server-side components of Reporting Services?
4. How will all of this change when SQL Server 2008 is released?

Maybe I should just use Microsoft Visual FoxPro, which is awful, but at least R&R Report Writer still supports it.

[Update 1/9/2008] Here are some initial answers to my questions:

1. BIDS runs in Visual Studio 2005. It's a "personality", just like C++ versus C#.
2. TBD
3. You have to install SQL Server 2005 Express Edition with Advanced Services SP2. In addition, when you do that installation, you have to specifically enable the features for Reporting Services. My recommendation is to enable everything. Don't forget to enable the subprojects. Make sure you install this BEFORE you install Visual Studio 2005 or 2008.
4. TBD

WinSxS Breaks Old Libraries

After my previous experiences with handling Windows Side-by-Side Assemblies (WinSxS) in remote debugging and Isolated COM, I thought I was actually starting to get a handle on how it worked. Today I got stuck on another WinSxS problem, this time while porting our application to Visual Studio 2008.

The problem was that the application would fail to fail to start, with an error about the manifest being wrong. I used sxstrace, a handy tool under Vista, to try and determine what was happening. Sxstrace generated 180 lines of information about Vista's attempt to find and load the correct assemblies. It ended up being too much information. The only obvious problem I saw was that one of the DLLs being loaded was from Visual Studio 2005, not 2008.

I used Depends to look at the file and received the same errors. I looked in the Event Viewer and saw this error:

Activation context generation failed for "s:\csi\xsales\debug\XSALES.EXE". Dependent Assembly Microsoft.VC90.DebugCRT, processorArchitecture="x86", publicKeyToken="1fc8b3b9a1e18e3b", type="win32",version="9.0.20706.1" could not be found. Please use sxstrace.exe for detailed diagnosis.

This is even stranger, because 20706 was the version ID of Visual Studio 2008 Beta 2. I'm running the final release.

I used Visual Studio to open XSales.exe in Resource mode so I could look at the manifest itself. Here I found references to four versions of DebugCRT. The question was, where were they coming from? My code makes no explicit reference to assembly versions.

What I discovered was that the intermediate manifests generated by the compiler and linker include assembly information from all objects used by the linker, including objects from libraries, of which I had two. One of my .LIB files was generated by VS2005 and another was generated by VS2008 Beta 2, which is what caused the references to old assembly versions. Once I rebuilt those .LIB files with VS2008, the problem went away.

The lesson learned from all of this is that .LIB files are no longer easily portable between versions if they rely on any of the CRT or MFC DLLs. The painful part of this is that the problem doesn't show up until you try and run the software because none of the development tools warn about the inconsistency.

[Update 11/19/2008] You can find another solution here.