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          

50 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. 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
  11. Hey Really Thanks for sharing the best information regarding cloud applications,hope you will write more great blogs.

    Oracle Fusion HCM Online Training

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

    ReplyDelete
  13. 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
  14. 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
  15. 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
  16. Your blog provided us with valuable information to work with. Each & every tips of your post are awesome. Thanks a lot for sharing. Keep blogging, vpnveteran.com

    ReplyDelete

  17. Great article, learned many things about email marketing from this blog, very informative. The Buy Email Database
    is one of the best email providers in USA.

    ReplyDelete
  18. A simple bookmarking tool that makes it easy to save, organize and share your favorite web pages. Access your bookmarks from any computer, phone or tablet. Listango works on all modern web browsers… https://weneedprivacy.com

    ReplyDelete
  19. This is a wonderful article, Given so much info in it, These type of articles keeps the users interest in the website, and keep on sharing more ... good luck. allerta privacy

    ReplyDelete
  20. Amazing post! I appreciate your hard work. Thank you for sharing. I have also share some use full information.
    Drone pro review
    mosquitron reviews
    eco beat earphones review
    Coolair review
    Coolair air cooler review

    ReplyDelete
  21. Great and really helpful. as we know that security of your computer has become the utmost concern for all, we as Kaspersky support US help you to protect your device from different issues like viruses and other server issues.For more help contact kaspersky support US +0-800-689-1486.

    ReplyDelete
  22. Hey great piece of information and very helpful.As we know that in current time security of your computer is very important, we as Kaspersky support help you to protect your computer from viruses and other common issues that you may face. Contact the Kaspersky support team at +0-800-689-1486 or visit our website http://www.kasperskysupporttech.com/ in case you need help.

    ReplyDelete
  23. This is an awesome post. Really very informative and creative contents. These concept is a good way to enhance the knowledge. I like it and help me to development very well. Thank you for this brief explanation and very nice information. Well, got a good knowledge.
    Selenium online training
    Selenium certification training
    Selenium online course
    Selenium training course

    ReplyDelete
  24. Thank you for taking the time and sharing this information with us. It was indeed very helpful and insightful while being straight forward and to the point...
    MSBI Certification Training

    ReplyDelete
  25. thanks for posting this nice information.please keep posting.
    we are proving Digital marketing course in Bangalore with Live Projects. 100% placement, 20+ modules, 10+ certifications, Great discounts on course fees.
    https://onlineidealab.com/digital-marketing-courses-in-bangalore/


    ReplyDelete
  26. thank you very much for this post. This post is really so useful for us. Mukul Sharma   When the film “Birds of Prey” was released on 07 Feb 2020, trade pundits projected it to gross $50 to $55 million during the opening weekend in the US and Canadian markets. Warner Bros, the distributors of the film had their own projection pegged at $45 million. However, It could muster only […]
    https://onlineidealab.com/warner-bros-loses-22-million-in-a-weekend-due-to-poor-seo/

    ReplyDelete
  27. very nice blogs!!! i have to learning for lot of information for this sites...Sharing for wonderful information. Thanks for sharing this valuable information to our vision. You have posted a trust worthy blog keep sharing

    Digital Marketing In Telugu
    Digital Marketing In Hyderabad
    internet marketing
    Digital marketing

    ReplyDelete
  28. Earn Rs.25000/- per month - Simple online Jobs - Are You Looking for Home-Based Online Jobs? - Are You a Student, Housewife, jobseeker ? - Are you ready to Work 1 to 2 Hours daily Online? - Do You need Guaranteed Payment Monthly? Then this is for You, - Clicking on their Advertisement E-mails. - Submitting their Data\'s online. - Reading their Advertisement Sms. - Filling Forms on their websites, etc,. FREE to Join >> http://dailyonlinejobs.com
    9PJK1587500784 2020-04-23 01:58:08

    ReplyDelete
  29. Earn Rs.25000/- per month - Simple online Jobs - Are You Looking for Home-Based Online Jobs? - Are You a Student, Housewife, jobseeker ? - Are you ready to Work 1 to 2 Hours daily Online? - Do You need Guaranteed Payment Monthly? Then this is for You, - Clicking on their Advertisement E-mails. - Submitting their Data\'s online. - Reading their Advertisement Sms. - Filling Forms on their websites, etc,. FREE to Join >> http://dailyonlinejobs.com
    9PJK1587500784 2020-04-23 01:58:08

    ReplyDelete
  30. You should have a great deal of pride recorded as a hard copy quality substance. I'm dazzled with the measure of strong data you have written in your article. I want to understand more.

    SEO services in kolkata
    Best SEO services in kolkata
    SEO company in kolkata
    Best SEO company in kolkata
    Top SEO company in kolkata
    Top SEO services in kolkata
    SEO services in India
    SEO copmany in India

    ReplyDelete
  31. 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. antique gold earrings designs , silver head pieces , mirror jewelry box , winter scarves for women

    ReplyDelete
  32. Wonderful blog, thanks for sharing this type of information. Your website gives the best idea about my interesting information. Any issue related to Online acting classes you can visit the ICE-Balaji website.

    ReplyDelete
  33. Office interior design can have a huge impact on the morale and productivity of existing staff, and the potential of attracting new staff. Homely Design Studio is one of the top Office interior Design Company in Mumbai aims to accurately reflect the client's brand values and culture of the office.

    Office Interior Designers Mumbai
    Office Interior Design Company in Mumbai

    ReplyDelete
  34. New Post! Very helpful information and thanks for sharing.
    PayMe India is an online lending marketplace for personal loans. Corporate employees can apply for online personal loans and get instant loan approval based on their social score, credit score, employment history, and KYC documents. It connects borrowers with multiple lending partners that enable them to choose from various loan products based on the interest rates and loan terms. It features an app-based platform that is available for both Android and IOS devices.

    ReplyDelete
  35. ne yazsam az kalır çok iyi

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

    Admission Open - Manthena American School Sharjah

    ReplyDelete
  37. Thanks for sharing informative article it really helpfull.

    ReplyDelete
  38. To express my gratitude for your insightful postings on a daily basis, I wanted to write you a short message. Joint discomfort is among the most common problems that adults and older people experience. People commonly find themselves in this comfort zone, and joint discomfort is one of the many possible causes of this. To learn more, see my blog at: https://ayursesha.com/blogs/news/common-reasons-for-joint-pain-and-how-to-relieve-it

    ReplyDelete
  39. This blog contains a wealth of valuable information! The step-by-step guide to withdrawing Bitcoin from a National Bitcoin ATM is truly enlightening. I appreciate how each step is clearly explained, catering to both beginners and experienced crypto users. This blog has equipped me with the knowledge and confidence to navigate the cryptocurrency world effectively. Furthermore, if you're eager to delve deeper, I recently stumbled upon a blog titled "How to Withdraw Bitcoin from a National Bitcoin ATM." It delves into the process in detail, providing practical tips and insights to enhance your Bitcoin ATM experience. I highly recommend giving it a read for even more valuable information. Keep up the fantastic work!

    ReplyDelete