Pages

Thursday, 27 September 2012

SQL SERVER : How to List All Email Subscription configured in SSRS ?

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
GO

DECLARE @Subscriptions TABLE (
                Report_OID  UNIQUEIDENTIFIER,
                ToList      VARCHAR(8000),
                CCList      VARCHAR(8000),
                BCCList     VARCHAR(8000),
                SubjectLine VARCHAR(8000))DECLARE @ExtensionSettings XML
DECLARE @Report_OID UNIQUEIDENTIFIER
DECLARE @idoc  INT
DECLARE SubscriptionList CURSOR FOR
SELECT Report_OID,ExtensionSettings 
    FROM subscriptionsOPEN SubscriptionList
    FETCH NEXT     FROM SubscriptionList INTO @Report_OID ,@ExtensionSettings
     WHILE (@@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          

4 comments:

  1. Hi, when running your above query I am getting the following error:
    Msg 208, Level 16, State 1, Line 10
    Invalid object name 'subscriptionsOPEN'.

    Sorry, I am new to SSRS and SQL so any insight would be appreciated.

    ReplyDelete
    Replies
    1. It is alignment issue . Please move open in the next line as shown below

      SELECT Report_OID,ExtensionSettings
      FROM subscriptions
      OPEN SubscriptionList

      Delete
    2. Hi John, I am looking for a similar query. But when I ran the above script, I get - Incorrect syntax near 'pivot'. Can you help. Thanks

      Delete
  2. Thank you, that worked of course. Next time I will look a little closer at the query. Much appreciated.

    ReplyDelete