Blog Post

SSRS Subscription E-Mail Error

,

It is that time again.  This time coming to you from a Mcdonald’s so my son can get out some energy, and it is 105 degrees outside.

This will solve what I consider a bug in how SSRS creates subscriptions.

Problem

Recently, I was tasked with creating an email subscription to a new SSRS report in an environment that I was not familiar with.  I have created my fair share of subscriptions in my day, and this one was very straightforward.

I found the report, clicked on Manage, and went to the Subscription page.  Clicked on New Subscription and filled in all the information, easy peasy.

The subscription is ready to go when the schedule kicks in the next day, or so I thought.

I did not try and send the email to the user listed in the failure results. Why do we care about the user that created the subscription?

Resolution

After researching and digging through the SSRS logs, I found that when you create a new subscription, the record is stored in the ReportServer databases Subscription table with the login account that created the subscription. This is pertinent to troubleshooting because if the account does not have an email address associated with it or does not have permission to send an email, your subscription will fail.

Then I decided to run a query on the Subscription table and joined the Users table to get the user name.

This report subscription shows TheUser has the account used to create the email subscription. And as we saw from the error and logs, this user does not have email permissions. We need to update that value in the Subscription table to a valid account in the Users table to fix the problem.

USE ReportServer
GO
SELECT
s.SubscriptionID, u.UserName AS TheUser, s.ModifiedDate, s.LastRunTime, s.[Description]
,c.[Name] AS TheReport
FROM
[Catalog] c
INNER JOIN
Subscriptions s ON s.Report_OID = c.ItemID
INNER JOIN
Users u ON u.UserID = s.OwnerID
ORDER BY 
u.UserName
/*
-- Update OwnerID for existing subscriptions
DECLARE @OldUserID uniqueidentifier
DECLARE @NewUserID uniqueidentifier
SELECT @OldUserID = UserID FROM dbo.Users WHERE UserName = 'Domainadm'
SELECT @NewUserID = UserID FROM dbo.Users WHERE UserName = 'Domainemailaccount'
UPDATE dbo.Subscriptions SET OwnerID = @NewUserID WHERE OwnerID = @OldUserID AND SubscriptionID = '3E42B6FD-9B21-4977-AE63-82D5C86D344F'
*/

This query will show you all the subscriptions with the owner account listed.  Then, an update statement is listed that will change the account from what was used to create the subscription to an account with proper permissions.

The post SSRS Subscription E-Mail Error appeared first on GarryBargsley.com.

Original post (opens in new tab)
View comments in original post (opens in new tab)

Rate

You rated this post out of 5. Change rating

Share

Share

Rate

You rated this post out of 5. Change rating