For any of you out there that have embrased or at the very least attempted to embrase the SQL Server 2005 Reporting Services Modeling and Report Builder you should definitly check out the newest release of Report Builder.

Report Builder 2.0 offers many new features including the Tablix table formating, multiple data sets and data sources. Many of the features of Business Intelligence Studio’s Report Designer were also brought over allowing for any report to be opened not just those designed in Report Builder.

One thing to not is that Report Builder 1.0 is still the Click Once application and will continue until possibly the release of the next Service Pack or possibly not til Kilimanjaro (SQL Server 2010?). For now it is available as a standalone client install available for download here:

For those out there that use Team Systems to manage database development you way want to check out the new Release Candidate which includes SQL Sever 2008 support. Yes, thats right, the original only supported though SQL Server 2005.

Looking for a good way to monitor your SQL Servers. Microsoft released the Management Pack (MP) for Operations manager 2007. This MP is capable of monitoring many of the key elements of SQL and Windows Server for CPU, Drive/File Space, Memory, SQL Agent Jobs, Blocking and tons of other goodies.

I recently ran into an issue with developing SSIS packages on a development server running SQL Server 2008 64 bit (X64). After intalling the 64 bit 10G Instant Client and Data Access Components (DAC), I was able to create a linked server and connect to as one would expect to an Oracle server.

The fun started when trying to develop against the same Oracle Servers in SQL Server Integration Services 2008 (SSIS). After recieving a slue of Driver and TNS Errors I found some compelling information. As it turned out (and I should have realized) the Business Intelligence Development Studio (i.e. BIDS, Visual Studio 2008) is a 32 bit application. It runs under the “Program Files (x86)” directory.

In my adventure to get through this I installed the 32 bit Oracle Instant Client and DAC side by side with the 64 bit varient. But the fun doesn’t stop here. As it turns our Oracle for one reason or another seems not to like the “Program Files (x86)” directory. Specifically it seems not to like the ‘(‘ and ‘)’ characters.

To get around this you need to run any programs from the directory using the old school methods of Progra~2. In other words to run and develop in BIDS from the following command: C:\Progra~2\Microsoft Visual Studio 9.0\Common7\IDE\devenv.exe

As it turns out SSIS when installed on a 64bit Server has several components that include:

When running a package from BIDS the process will use the 32 bit edition and the x86 Oracle drivers. the same holds true when running the package interactively from Managment Studio (SSMS). However, when scheduled the package will run in 64 bit and utilize the 64 bit drivers.

Now with this one could assume that you could call the command direct running the 32 bit dtexec.exe for other 32 bit drivers and I plan to test in the very near future with a legacy driver (Rumba/AS400).

For any of you that work closely with Reporting Services, I am sure you can vouch that the system offers a great amount of flexibility and capability. At the same time though you also have probably encountered your fair share of strange, random and undocumented issues. One today I found happened with a Subscription.

Failed sending mail: The user or group name ‘Domain\UserNoMore’ is not recognized

Recently, we had a user leave the company. For the sake of this article we will happily refer to him as UserNoMore. When you create a subscription you are not propted to define an owner. You get the happy assignement automagically. When the user account is deactivated authentication ceases and emails and file archives begin to fail.

In my experience here it took a little finess and hackery to correct. Now let me first say I niether condone nore dismiss the need of making direct changes to data. So in this case after attempting any known fix or finding any interface I determined the fix would be in a bit of DML. Really the fix was pretty simple. Find the subscription, determine a different user to own, update the subscriptions. In this case I selected the service account for Reporting Services so I wouldn’t have to worry later (and I guess technically you could do this for all subscriptions if thats how you shop is set security wise).

In the ReportServer$InstanceName database for reporting services you will find 3 key tables:

Catalog – Reports, Folders and basically all Items
Subscriptions – The scheduled report processes
User – Well…any user or group in Reporting Services

Step 1- Get the Report ID in Question

Step 2 – Find the Subscription

Step 3 – Select another User

Step 4 – Update the Subscription

Really an easy fix…not clean but it worked for me. Feel free to provide feedback.

Enjoy!