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

siera_gld gravatar image

siera_gld
1k 80 84 85

(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

siera_gld gravatar image

siera_gld
1k 80 84 85

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:

x12
x9
x7

asked: Mar 01, 2012 at 11:50 PM

Seen: 1519 times

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