Subscribe to this blog
Briefcase
Print
Often I used to get request from my business managers to get a list of all subscriptions configured in SSRS (SQL Server Reporting Service) along with email distribution list. I did not find any option in report server report manager interface to achieve this. I usually run the below query on the reporting server repository database to get the list.
USE REPORTSERVER_REPOSITORY
GODECLARE @Subscriptions TABLE ( Report_OID UNIQUEIDENTIFIER, ToList VARCHAR(8000), CCList VARCHAR(8000), BCCList VARCHAR(8000), SubjectLine VARCHAR(8000))DECLARE @ExtensionSettings XMLDECLARE @Report_OID UNIQUEIDENTIFIERDECLARE @idoc INTDECLARE SubscriptionList CURSOR FORSELECT Report_OID,ExtensionSettings FROM subscriptionsOPEN SubscriptionList FETCH NEXT FROM SubscriptionList INTO @Report_OID ,@ExtensionSettingsWHILE (@@FETCH_STATUS=0)
BEGIN EXEC sp_xml_preparedocument @idoc OUTPUT, @ExtensionSettings INSERT INTO @Subscriptions SELECT @Report_OID,[TO],[CC],[BCC],[Subject] FROM ( SELECT * FROM OPENXML (@idoc, '/ParameterValues/ParameterValue') WITH (Name NVARCHAR(100) 'Name', Value NVARCHAR(100) 'Value') ) AS SourceTable pivot ( MAX(value) FOR [Name] IN ([TO],[BCC],[CC],[Subject]) ) AS pivottable EXEC sp_xml_removedocument @idoc FETCH NEXT FROM SubscriptionList INTO @Report_OID ,@ExtensionSettings
END
CLOSE SubscriptionList DEALLOCATE SubscriptionList
SELECT c.path,c.name,s.Tolist,s.cclist,s.bcclist,s.subjectline FROM Catalog c INNER JOIN @Subscriptions s ON c.ItemID = s.Report_OID ORDER BY [path], Name
This will list the all reports configured for subscriptions with following details of Report Path, Report Name, ToList,CCList,BCCList and subject line of email.
If you liked this post, do like my page on FaceBook www.PracticalSqlDba.com
Loading comments...