October 25, 2011 at 6:59 am
Need to remove an eMail Address from all Report subscription notifications. (our lead Report manager left the company and we need to remove her eMail address)
Is there an SSRS catalog table which houses all eMaill addresses for report subscription notifications?
I'm perusing ReportServer's Subscriptions, Notifications, Users tables and nothing seems to stand out?
I'd like to write a query to identify ALL of the Report subscriptions where her eMail address was included in the notification..
EG. SELECT * from ReportServer.dbo.SomeTableName where column_??? = 'her_eMailAddress@myCompany.com'
thanks in advance !!
October 25, 2011 at 9:54 am
This should find pretty much everything EXCEPT data driven subscriptions where the data is being pulled from a table in the database. For example I have a subscription that reaches into an employee database table and send individualized reports to each person in a specific department. The employee info is not part of the data driven subscription query, or hard coded into the subscription so it would not show up in the query below. But mostly, the query below gets you where you want to be:
DECLARE @Email VARCHAR(50)
SET @Email = 'email@email.com'
SELECT cat.[Path]
,cat.[Name]
,sub.SubscriptionID
,CAST(extensionSettings AS XML).value('(//ParameterValue/Value)[1]',
'varchar(max)')
FROM dbo.Catalog AS cat
INNER JOIN dbo.Subscriptions AS sub
ON cat.ItemID = sub.Report_OID
WHERE CAST(extensionSettings AS XML).value('(//ParameterValue/Value)[1]',
'varchar(max)') LIKE '%' + @Email + '%'
ORDER BY cat.[Path]
,cat.[Name]
SELECT cat.[Path]
,cat.[Name]
,sub.SubscriptionID
FROM dbo.Catalog AS cat
INNER JOIN dbo.Subscriptions AS sub
ON cat.ItemID = sub.Report_OID
WHERE extensionSettings LIKE '%' + @Email + '%'
ORDER BY cat.[Path]
,cat.[Name]
This query should be run against your reporting services database which is typically on the server that hosts the Report Manager
October 25, 2011 at 10:38 am
thank you -- I found her subscriptions using your code! much appreciated
October 25, 2011 at 2:06 pm
I forgot to point out that the first query will only look at the To email address. The second query will look for the email address any field.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply