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.


DECLARE @Subscriptions TABLE (
                Report_OID  UNIQUEIDENTIFIER,
                ToList      VARCHAR(8000),
                CCList      VARCHAR(8000),
                BCCList     VARCHAR(8000),
                SubjectLine VARCHAR(8000))DECLARE @ExtensionSettings XML
SELECT Report_OID,ExtensionSettings 
    FROM subscriptionsOPEN SubscriptionList
    FETCH NEXT     FROM SubscriptionList INTO @Report_OID ,@ExtensionSettings
    EXEC sp_xml_preparedocument @idoc OUTPUT, @ExtensionSettings
    INSERT INTO @Subscriptions
        SELECT @Report_OID,[TO],[CC],[BCC],[Subject]
        SELECT *
            FROM OPENXML (@idoc, '/ParameterValues/ParameterValue')
                    WITH (Name NVARCHAR(100) 'Name',
                        Value NVARCHAR(100) 'Value')
        ) AS SourceTable
                    MAX(value) FOR [Name] IN ([TO],[BCC],[CC],[Subject])
        ) AS pivottable
    EXEC sp_xml_removedocument @idoc
   FETCH NEXT  FROM SubscriptionList INTO @Report_OID ,@ExtensionSettings


            DEALLOCATE SubscriptionList

SELECT c.path,,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          


  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.

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

      SELECT Report_OID,ExtensionSettings
      FROM subscriptions
      OPEN SubscriptionList

    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

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

  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.

  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.

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

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

  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

  8. hi,

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

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

  10. Nice post




  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

  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

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

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

    Oracle Fusion HCM Online Training

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

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

  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

  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.

  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

  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.

  24. 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,

  25. Indus Valley Mukteshwar constructed & delivered several deluxe cottages & apartments in Mukteshwar, Nainital. Contact for more information.

    cottages in nainital for sale

  26. xender
    xender app
    xender apk
    xender downloading
    xender install
    This is said to be the best sharing app on the entire internet. You can share any kind of files from any place at all times.

  27. شركة لمسات جدة تعتبر افضل شركة تنظيف خزانات بجدة وتعد من اقوي شركات مكافحة الحشرات بجدة وبالفعل افضل شركة مكافحة حشرات بالطائف لأنها شركة متميزة ولديها فريق عمل متخصص وتستخدم دائما ما شركة صيانة خزانات بجدة ما يناسب وضعها ومكانتها فى سوق العمل شركة تنظيف منازل بجدة , نحن نقدم اليكم خدمتنا , فاذا استعنت بنا شركة تنظيف منازل بمكة او اتصلت على شركة تنظيف خزانات بالمدينة المنورة فانت الرابح الاكبر مع شركات تنظيف المنازل لا تترد فى الاتصال علينا وسوف نصلك فى اى مكان

  28. Packers and Movers Chennai Give Safe and Reliable ***Household Shifting Services in Chennai with Reasonable ###Packers and Movers Price Quotation. We Provide Household Shifting, Office Relocation, ✔✔✔Local and Domestic Transportation Services, Affordable and Reliable Shifting Service Charges @
    Packers And Movers Chennai

  29. Packers and Movers Bangalore as a Services providing company can make all the difference to your ###Home Relocation experience. Bangalore based Company which offers versatile solutions, Right team that easily reduce the stress associated with a ✔✔✔Household Shifting, ***Vehicle Transportation. we help things run smoothly and reduce breakages and offer you seamless, Affordable, Reliable Shifting Services, Compare Shifting Charges @
    Packers And Movers Bangalore


  30. 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.

  31. 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…

  32. 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

  33. 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

  34. 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.

  35. 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 in case you need help.

  36. 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

  37. 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

  38. Very interesting blog I would like to thank you for the efforts you have made in writing this article. I am hoping the same best work from you in the future as well.

    Oracle Fusion HCM Online Training

  39. 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.

  40. 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 […]

  41. 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

  42. This Website is great for group chat and conversation. Thanks for sharing with us. like this website so much it's really awesome.I have also gone through your other posts too and they are also very much appreciate able and I'm just waiting for your next update to come as I like all your posts.

    The Other Track
    Radiation Stopper Pro
    Top gadgets to buy in 2020
    Q Grip Wax remover
    UV Clenizer Zoom Disinfectant robot
    X watch smartwatch
    Keysmart key oranizer

  43. 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 >>
    9PJK1587500784 2020-04-23 01:58:08

  44. 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 >>
    9PJK1587500784 2020-04-23 01:58:08

  45. 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

  46. Dinkcart is famous and reputed company in Delhi/NCR for manufacturing of printed t-shirts. Get customized t shirts for various purpose and occasions.

    T shirt manufacturer in Delhi
    Mom –Son Tees Online

  47. Aaditri Technology is a leading website design, web development company in Delhi, India we offer custom website development and all types of digital marketing services.

    Best Cms Development Company in India
    Ecommerce website development Delhi
    Travel Website Design Company
    Best Seo Company in Delhi

  48. Girlsncurls is an online shop where you can shopping for Clothing & Accessories from a great selection of Lingerie, Nightwear, Loungewear and more at everyday low prices.

    Women's Cotton Bra Online
    Womens Bra Panty Sets Online
    Full Figure Bra Online
    Womens T shirts Online

  49. 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

  50. 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.

  51. thank you for the information provided, we are waiting for the next info at cara mengugurkan kandungan

  52. Ucuz, kaliteli ve organik sosyal medya hizmetleri satın almak için Ravje Medyayı tercih edebilir ve sosyal medya hesaplarını hızla büyütebilirsin. Ravje Medya ile sosyal medya hesaplarını organik ve gerçek kişiler ile geliştirebilir, kişisel ya da ticari hesapların için Ravje Medyayı tercih edebilirsin. Ravje Medya internet sitesine giriş yapmak için hemen tıkla:

    İnstagram takipçi satın almak için Ravje Medya hizmetlerini tercih edebilir, güvenilir ve gerçek takipçilere Ravje Medya ile ulaşabilirsin. İnstagram takipçi satın almak artık Ravje Medya ile oldukça güvenilir. Hemen instagram takipçi satın almak için Ravje Medyanın ilgili sayfasını ziyaret et: instagram takipçi satın al

    Tiktok takipçi satın al istiyorsan tercihini Ravje Medya yap! Ravje Medya uzman kadrosu ve profesyonel ekibi ile sizlere Tiktok takipçi satın alma hizmetide sunmaktadır. Tiktok takipçi satın almak için hemen tıkla: tiktok takipçi satın al

  53. Die Technologie entwickelt sich schneller denn je und schneller als Sie denken. Diese aufkommende Tekhnologie wird unsere Lebensweise verändern

  54. 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

  55. 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.

  56. Dieses Mal werde ich euch allen Informationen zur Verfügung stellen. Für diejenigen, die weiterhin wissen möchten, was in der Welt der Technologie jetzt passiert, können Sie mehrere Blogs Laptops Und Smartphones sehen, die weiterhin aktiv Beiträge über die neueste Technologie bereitstellen.

  57. Takipçi satın al! Sende aşağıdaki bağlantıları kullanarak en güvenli takipçi satın alma sitesi ile takipçi satın al. Tıkla hemen sende instagram takipçi satın al:

    1- takipçi satın al

    2- takipçi satın al

    3 - takipçi satın al

  58. ne yazsam az kalır çok iyi

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

    Admission Open - Manthena American School Sharjah

  60. Thanks for sharing informative article it really helpfull.

  61. Very good article. This contains a lot of information. Thank you very much. Oracle Fusion SCM Training