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          

31 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
  14. if you have any problem related to the router then you can contact us we are here to provide you the complete solution to your problem.
    d link router customer support

    ReplyDelete
  15. Routers are small electronic devices that connect multiple computer networks together via either wired or wireless connections. if any query to related Router then you can contact TP-Link Router Support Number

    ReplyDelete
  16. Are you searching for certified industry professional expert on SAP BO Online Training institute ?

    ReplyDelete
  17. Hey Really Thanks for sharing the best information regarding cloud applications,hope you will write more great blogs.

    Oracle Fusion HCM Online Training

    ReplyDelete
  18. very interesting and most amazing post and giving details to be help me thanks
    Candid photographers in jaipur

    ReplyDelete
  19. just information we only provide information for those who need it

    ReplyDelete
  20. Your work here on this blog has been top notch from day 1. You've been continously providing amazing articles for us all to read and I just hope that you keep it going on in the future as well. Cheers! find out more

    ReplyDelete
  21. Very cozy looking rooms. Let me know if your going to Mexico. Oh and btw. you should read our Tipping in Mexico guide if you do. It will save you a lot of awkward moments. internetprivatsphare.at

    ReplyDelete
  22. The article you have shared here very awesome. I really like and appreciated your work. I read deeply your article, the points you have mentioned in this article are useful vpn for expats

    ReplyDelete
  23. This is a truly good site post. Not too many people would actually. the way you just did. I am really impressed that there is so much information about this subject that have been uncovered and you’ve done your best. with so much class. If wanted to know more about green smoke reviews. than by all means come in and check our stuff. lemigliorivpn.com

    ReplyDelete