x

Parse Emails from SSRS

I am trying to parse all the emails from the Subscriptions - I need to create a table and build an email notifying the users of some changes...

I am not familiar with looping through and using an index in order to create a table that looks something like

ReportName Email Customers EmailAddy1 Customers EmailAddy2 Products EmailAddy1 Products EmailAddy2

     SELECT DISTINCT
 subs.report_oid, 
 c.Path,
 c.[Name]as ReportName,
 replace(replace(CAST(CAST(extensionsettings as XML).query('/ParameterValues/ParameterValue/Value[../Name =''TO'']') as varchar(8000)), '</Value>', ''), '<Value>', '') + '; ' AS TO_RECPTS,
 replace(replace(CAST(CAST(extensionsettings as XML).query('/ParameterValues/ParameterValue/Value[../Name =''CC'']') as varchar(8000)), '</Value>', ''), '<Value>', '') + '; ' AS CC_RECPTS
 into #subs
 FROM
 ReportServer.dbo.Subscriptions subs with(nolock)
         join ddcd3035.reportserver.dbo.catalog c
         on c.itemid = subs.report_oid


more ▼

asked Mar 01, 2012 at 11:50 PM in Default

avatar image

siera_gld
1k 82 88 93

(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

Here is my attempt to loop through the emails and add to another table a list of reports and emails

 SELECT DISTINCT
 subs.Subscriptionid SUB_ID, 
 subs.report_oid, 
 c.Path,
 c.[Name]as ReportName,
 replace(replace(CAST(CAST(extensionsettings as XML).query('/ParameterValues/ParameterValue/Value[../Name =''TO'']') as varchar(8000)), '</Value>', ''), '<Value>', '') + '; ' AS TO_RECPTS,
 replace(replace(CAST(CAST(extensionsettings as XML).query('/ParameterValues/ParameterValue/Value[../Name =''CC'']') as varchar(8000)), '</Value>', ''), '<Value>', '') + '; ' AS CC_RECPTS
 into #subs
 FROM
 ReportServer.dbo.Subscriptions subs with(nolock)
         join ddcd3035.reportserver.dbo.catalog c
         on c.itemid = subs.report_oid
 
 where subs.lastruntime >='2011-11-01'
 
 ALTER TABLE #subs
 ADD URL VARCHAR(1000)
 
 UPDATE #subs 
 SET URL = 'unknown'
 
 Create Table #Subscriptions (
 SUB_ID varchar(50),
 REPORT_OID varchar(50),
 Email Varchar(100),
 ReportPath Varchar(1000),
 ReportName Varchar(1000),
 URL Varchar(1000),
 UnsubscribeURL Varchar(1000) )
 
 
 
 
 begin 
     declare @url varchar(max)
 
                     set @url = 'http://LOCALHOST/Reports/Pages/Report.aspx?ItemPath=%2f'
 
         WHILE EXISTS (SELECT TOP 1 SUB_ID FROM #subs WHERE URL = 'unknown' )             
                 
             BEGIN 
                     DECLARE @email VARCHAR(MAX)
                     DECLARE @origpath VARCHAR(MAX)
                     DECLARE @report VARCHAR(MAX)                    
                     DECLARE @SUB VARCHAR(MAX)
                     
                     SET @email =  (SELECT  MIN(TO_RECPTS) FROM #subs  where url ='unknown') 
                     SET @origpath = (SELECT  MIN([Path]) FROM #subs  where url ='unknown')
                     SET @report = (SELECT  MIN(ReportName) FROM #subs  where url ='unknown')
 
                     DECLARE @temp VARCHAR(MAX)
 
                     SET @temp = @email
                     DECLARE @index INT
 
                     SET @index = charindex(';', @email)
                           SET @email = substring(@email, @index+1, len(@email)-1)
                           --Loop through the EMAIL LIST
                           WHILE (@index <> 0)
                           BEGIN       
                                 SET @index = charindex(';', @email)
                                 IF @index <>0
                                       BEGIN
                                             set @temp = @temp + substring(@email, 1, charindex(';', @email)-1)
                                             SET @email = substring(@email, @index+1, len(@email)-1)
                                             set @temp = @temp 
                                       END
                           END 
                     SET @temp = @email
                 
 
 
                     INSERT INTO #subscriptions (Email, ReportName, URL )
                 
                     SELECT  @temp
                             ,@report
                             ,@url
                     FROM #subs
                     WHERE SUB_ID = @SUB
 
                 
             DELETE FROM #subs
             WHERE SUB_ID = @SUB
                     
 
             END 
      SET @SUB =  (SELECT TOP 1 SUB_ID FROM #subs  WHERE URL ='UNKNOWN')
 
 
 END
more ▼

answered Mar 02, 2012 at 01:02 AM

avatar image

siera_gld
1k 82 88 93

Mar 02, 2012 at 04:05 AM siera_gld
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x20
x14
x9

asked: Mar 01, 2012 at 11:50 PM

Seen: 1917 times

Last Updated: Mar 02, 2012 at 07:25 AM

Copyright 2016 Redgate Software. Privacy Policy