SSRS Subscriptions – Internals

This URL talks about the internals of SSRS Subscriptions.For someone to wants to debug data driven subscriptions, this would be a great help.

http://blogs.msdn.com/b/deanka/archive/2009/01/13/diagnosing-and-troubleshooting-subscriptions.aspx

-Jegan

Advertisements
Posted in DataFeeds, SSRS | Leave a comment

Microsoft.ReportingServices.Diagnostics.Utilities.UnknownUserNameException: The user or group name is not recognized.

Problem:

Recently our Data Driven Subscriptions started failing with the following error message (as in SSRS Error Logs found in Program Files\Microsoft SQL Server\MSRS10.SqlServer\Logfiles\….).

library!WindowsService_0!960!01/07/2011-14:17:59:: e ERROR: Error processing data driven subscription: Microsoft.ReportingServices.Diagnostics.Utilities.UnknownUserNameException: The user or group name ‘<UserName>’  is not recognized at Microsoft.ReportingServices.Library.Native.NameToSid(String name) at Microsoft.ReportingServices.Library.ActiveSubscription.CreateDataDrivenNotification(SubscriptionImpl subscription, Guid activationID, Settings settings, ParamValues paramValues) at Microsoft.ReportingServices.Library.DataDrivenSubscriptionHandler.DataDrivenSubscriptionWorker(Object o)

<UserName> mentioned above was the username of one employee who recently left our organization.

Diagnosis:

I had no idea what was going on and the first task was to identify which assembly contained this class – Microsoft.ReportingServices.Library.ActiveSubscription. After loading few assemblies from ReportServer bin folder (<Drive>:\Program Files\Microsoft SQL Server\MSRS10.MSSQLSERVER\Reporting Services\ReportServer\bin)  to see which assembly contained these classes, the assembly containing the calls was – ReportingServicesServer.dll. This assembly is used by the SSRS Windows Service (ReportingServices.exe) during subscription delivery.

Next step is to generate the code of this assembly and see what is happening within these method calls. I have used Reflector and File Disassembler (Reflector AddIn) to generate the code of this assembly and the code which causes the problems is as below:

public void CreateDataDrivenNotification(SubscriptionImpl subscription, Guid activationID, Settings settings, ParamValues paramValues)

{

using (InstrumentedSqlCommand command = base.NewStandardSqlCommand(“CreateDataDrivenNotification”))

{

command.Parameters.AddWithValue(“@SubscriptionID”, subscription.ID);                           command.Parameters.AddWithValue(“@ActiveationID”, activationID);                                  command.Parameters.AddWithValue(“@ReportID”, subscription.ReportID);                        command.AddParameter(“@Locale”, SqlDbType.NVarChar,        subscription.m_subscriptionCulture);                  command.AddParameter(“@ExtensionSettings”, SqlDbType.NText,                             ParameterValueOrFieldReference.ThisArrayToXml(settings.ToSoapParameterValueArray()));                command.AddParameter(“@Parameters”, SqlDbType.NText, paramValues.ToXml(false));                command.Parameters.AddWithValue(“@LastRunTime”, subscription.m_lastRunTime);                command.AddParameter(“@DeliveryExtension”, SqlDbType.NVarChar, subscription.DeliveryExtension);                command.Parameters.AddWithValue(“@OwnerSid”, Microsoft.ReportingServices.Library.Global.NameToSid(subscription.Owner)).SqlDbType = SqlDbType.VarBinary;                command.AddParameter(“@OwnerName”, SqlDbType.NVarChar, subscription.Owner.UserName);                command.Parameters.AddWithValue(“@OwnerAuthType”, (int) subscription.Owner.AuthenticationType);                command.Parameters.AddWithValue(“@Version”, subscription.m_version);                command.ExecuteNonQuery();            }        }

In this code there is a call to a method, NameToSid which tries to get Security Identifier of a User Name, which owns the Subscription. In this case, since the employee left the company, his user id was deleted. Since the User ID is not there anymore, an exception is thrown.

Fix:

Subscription related data is stored in ReportServer.dbo.Subscription table.This UserId is set as owner of the subscription in ReportServer.dbo.Subscriptions Owner_ID column  (Refers to ReportServer.dbo.Users.UserID). Now the solution is to update the OwnerId field in Subscriptions table to another user [presumably a service account or the username of a person who will never leave the company ;-)].

In this case we chose the latter. We used the following SQL to update the subscription ownership is below:

UPDATE ReportServer.dbo.Subscriptions SET OwnerID = (SELECT UserID FROM  ReportServer.dbo.Users WHERE UserName ='<Service Account UserName>’)  WHERE OwnerID = (SELECT UserID FROM ReportServer.dbo.Users WHERE UserName = ‘<Deleted UserName>’)

Since our automated deployment (to deploy subscriptions) was done using the credentials of this user, his userid was set as Subscription owner.

Googling for Change SSRS Subscription owner, brings this URL..

http://blogs.msdn.com/b/miah/archive/2008/07/10/tip-change-the-owner-of-report-server-subscription.aspx

Hmmm. If at all I knew what the problem was in the first place …:(

Posted in SSRS | Leave a comment

SSRS Data Driven Subscriptions – Queueing Mechanism

When thousands of data driven subscriptions are fired in SSRS, they are stored in ReportServer.dbo.Notifications table. Based on the workload of SSRS Service, subscriptions are picked from this table for processing.

This means even if SSRS Server / Windows Service is restarted, any data driven subscriptions initiated (but not actively running) would still be delivered. SSRS Subscription mechanism seems to be robust enough to send (in our case it is SFTP) many thousands of subscriptions without any problem.

That’s pretty good !

Posted in SSRS | Leave a comment

ForEach Loop Container – Continue On Error

Download Sample Package and SQL Files[Extract the zip file – SSISContinueOnError.zip containing SQL Files to C:\EasyBI such that all SQL files reside directly within C:\EasyBI\SSISContinueOnError]

Lets say we use For Each Loop Container for looping around a set of files – say 3 files – and do an operation  – like running the SQL text in all the files against a DB Connection. In case if the operation fails in the 2nd file, the container will fail and stop processing the remaining files. So 3rd file would not process by default.

How can we ignore the Error in one of the files and Continue processing other files? To do this, Lets understand why the For Each Loop container fails in the first place.

For Each Loop Container fails because the failing tasks in the container propagates / bubbles the error to the parent container. To ensure the processing continues, we need to stop the propagation.

We need to set the System::Propagate property of the SQL Task’s On Error Handler to false. Refer this URL for detailed information. This setting ensures that the SQL task Does Not Propogate error to the parent container (in this case For Each Loop Container).

The same behaviour would be applicable for any other type of For Each Loop Enumerator such as ADO Enumerator.

Download the package and try yourself.

Posted in SSIS | Leave a comment

Where is SSRS RDL Content Stored in ReportServer Database?

When an SSRS Report (.RDL) is deployed to SSRS Report Server, this is stored in SSRS Database ReportServer tables.

To retrieve SSRS RDL from ReportServer DB, use the following SQL by connecting to  Reporting Service SQL server.

SELECT [Path], Name, CAST(CAST(Content AS VARBINARY(MAX)) AS VARCHAR(MAX)) AS RdlXML FROM dbo.Catalog WHERE Name=‘<ReportNameHere>’

Posted in SSRS | Leave a comment

Blog on Microsoft BI

Hi,

I am Jegan, working as a BI developer in Bristol, UK. This is a blog focussing on BI- specifically Microsoft BI -SSIS, SSRS, SSAS. I will also be covering Automated deployment using MSBuild, Automated testing of SSRS reports & SSIS packages, internals of SSRS and SSIS.

This is an attempt to make BI easy for the developers first. I will try to make BI easy for end users later on ;).

Posted in Uncategorized | Leave a comment