March 21, 2011 at 10:20 am
Where can the subscriber information be found?
I have a user who left for greener pastures, now we need to find out all the subscription that he received so we can erase his user id/email address from the subscriptions. I do not want to assign an individual to manually search this information through the several hundred subscriptions.
March 21, 2011 at 12:51 pm
I don't think this will find cc or bcc, but I am not sure. For data driven subscriptions, you will need to query the table where the that particular subscription data is stored.
Run this against your reporting services database to find all subscriptions delivered via email to the recipient.
DECLARE @Email VARCHAR(50)
SET @Email = 'EmailAddressOfThePersonYouAreLookingFor'
SELECT cat.[Name]
,cat.[Path]
,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 + '%'
March 21, 2011 at 1:19 pm
Thanks Daniel. It filled the ticket!!
March 21, 2011 at 1:22 pm
I just added a few more notes above the script that you may want to note.
I just ran a couple more quick tests. For the CC change the parameter to [2] for the BCC change it to [3].
Again, for data driven subs you will have to dig into where the data for the subscription is stored.
March 22, 2011 at 7:11 am
Thanks again. The additional information is good to know for future clean ups especially when the users catch on to the data driven subscriptions. For now only our reports developers create this style of subscription.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply