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          

18 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
  3. That's a useful ting to know. I don't know your managers need it but in many sectors there are always several ways to make use of an email subscription list along with other relevant information. Thanks for sharing.

    ReplyDelete
  4. Good query. I have a question one of the jobs has email to listed as |TO| but the status has bunch of email IDs. How to query these email ids.

    ReplyDelete
  5. Thanks for sharing this query. Even though I am a coder I have always struggled with writing complex mysql queries.

    ReplyDelete
  6. thanks. This query sharing really helps me, hope it will solve my problem. Thank you again.

    ReplyDelete
  7. Thanks for sharing such a great information. It’s was really nice and informative.
    Our specialists deliver high-quality of knowledge on Oracle Fusion Tutorial application. We tend to area unit aforementioned to be leading on-line trainers across everywhere the globe.
    Oracle Fusion procurement Coaching Center
    Oracle Fusion procurement Online Coaching

    ReplyDelete
  8. hi,

    This is good article. thanks for sharing such a good article.
    oracle fusion SCM online training

    ReplyDelete
  9. thanks a lot but i got this error
    Cursor is not open

    ReplyDelete
  10. Nice post http://www.selfcarinsurance.com

    ReplyDelete
  11. https://kdp.amazon.com/community/profile.jspa?editMode=true&userID=1424531
    https://kdp.amazon.com/community/profile.jspa?editMode=true&userID=1427388
    https://kdp.amazon.com/community/profile.jspa?editMode=true&userID=1428236
    https://tmblr.co/ZcaPoj2Bk7Kpf
    https://tmblr.co/ZcaPoj2Bk7RDQ

    ReplyDelete
  12. https://tmblr.co/ZcaPoj2Bk7ZaB
    https://tmblr.co/ZcaPoj2BkA5WZ
    https://tmblr.co/ZcaPoj2BkBsuV
    https://tmblr.co/ZcaPoj2BkKQCs
    https://java.net/people/1214494-khairyayman
    https://java.net/people/1214497-radwaayman

    ReplyDelete
  13. https://challenges.openideo.com/profiles/1119874714489381863241486229946090
    http://www.dead.net/member/khairyayman
    https://vimeo.com/user54212503
    https://mootools.net/forge/profile/naklafshdmam
    http://bionumbers.hms.harvard.edu/bionumber.aspx?&id=113190

    ReplyDelete