SSRS

Report Builder 2.0

Posted in SQL Server, SSRS, Tools on November 4th, 2008 by speeddba – Be the first to comment

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: http://www.microsoft.com/downloads/details.aspx?FamilyID=9f783224-9871-4eea-b1d5-f3140a253db6&DisplayLang=en

Microsoft SQL Server Management Pack for Operations Manager 2007

Posted in Replication, SQL Server, SSIS, SSRS on November 4th, 2008 by speeddba – Be the first to comment

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.

Check out more and download here: http://www.microsoft.com/downloads/details.aspx?FamilyID=8c0f970e-c653-4c15-9e51-6a6cadfca363&DisplayLang=en

Failed sending mail: The user or group name 'Domain\User' is not recognized

Posted in SQL Server, SSRS on October 17th, 2008 by speeddba – Be the first to comment

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

SELECT [ItemID], [Name]
FROM [ReportServer$InstanceName].[dbo].[Catalog]
WHERE [Name] ='MonthlySalesReport'
and [Type] = 2 --Report Item Type

Step 2 – Find the Subscription

SELECT [SubscriptionID],[OwnerID],[Report_OID]
FROM [ReportServer$InstanceName].[dbo].[Subscriptions]
WHERE [Report_OID] = '1551AA5B-0FF1-494B-8725-73E22AA34D9F'

Step 3 – Select another User

SELECT [UserID],[UserName]
FROM [ReportServer$InstanceName].[dbo].[Users]
WHERE [UserName] = 'Domain\RSServiceAccount'

Step 4 – Update the Subscription

UPDATE [ReportServer$InstanceName].[dbo].[Subscriptions]
SET [OwnerID] = '6BDB4964-EE28-4E96-BCB2-E4509FAC909B'
WHERE [OwnerID] = '3D272CE2-A9B3-4B89-8993-C72E8D43A6EE'
AND [Report_OID] = '1551AA5B-0FF1-494B-8725-73E22AA34D9F'

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

Enjoy!