locked
SSRS email addresses for subscriptions RRS feed

  • Question

  • Good day,

    I have a list of email addresses (well over 200). I wish to find all reports subscribed to individual email addresses. 

    I am using the below script:

    use ReportServer 

    select distinct a.Path , 'name@home.com' as [email]
    from Catalog as a
    inner join Subscriptions  as b on b.Report_OID=a.ItemID
    where ExtensionSettings like '%name@home.com%'

    How can I iterate this over the 200 email addresses?

    Please assist,

    Many thanks

    Reagrds

    Zimiso

    Wednesday, January 4, 2017 10:59 AM

Answers

  • Hi Zimiso,

    Here is the updated one and It should work fine.

    declare @tsql nvarchar(1000)
    declare @loopstart int
    declare @loopend int
    declare @email nvarchar(400)
    
    set @loopstart = (Select min(ID) from EmailList)
    set @loopend = (Select max(ID) from EmailList)
    
    Create table tempFinalList
    (
    [Path] nvarchar(2000),
    [EmailId] nvarchar(400)
    )
    
    While (@loopstart < = @loopend)
    Begin
    set @email = (Select EmailID from EmailList where ID = @loopstart)
    
    Set @tsql = ' select distinct a.Path , '+ '''' + @email + ''''+ ' as [email]
    from Catalog as a
    inner join Subscriptions  as b on b.Report_OID=a.ItemID
    where ExtensionSettings like ' + ''''+ '%'  + @email + '%' + ''''
    
    Insert Into tempFinalList
    EXEC(@tsql)
    
    Set @loopstart = @loopstart + 1
    
    End


    Kindly mark the reply as answer if they help

    • Marked as answer by RonTech1 Thursday, January 5, 2017 5:05 AM
    Thursday, January 5, 2017 4:56 AM

All replies

  • Hi Zimiso,

    This is quite easy using TSQL. You can store those 200 email IDs into a table with an Identity column. (If you have those list of emailIds in text of excel file; you can Import them into a table in database.)

    Once the List of emailIDs is in a table, write a while loop and execute your main query and insert output into a table. Finally select from this table.

    Let me know if you need help in writing this logic.

    Thanks,


    Kindly mark the reply as answer if they help

    Wednesday, January 4, 2017 12:39 PM
  • Thank you for the response; could you please assist with teh logic?

    Many thanks

    Regards

    Zimiso

    Wednesday, January 4, 2017 12:43 PM
  • Step 1: Create a table using below TSQL

    use ReportServer
    
    Create Table EmailList
    (
    ID Int identity(1,1),
    EmailID nvarchar(400) not null
    )
    

    Step 2: Now Import List of emails you have into above table.

    Step 3: Once Imported; verify the data looks correct into this table. Use Query

    use ReportServer

    select * from EmailList

    Step 4: Once data is verified, Execute below query:

    use ReportServer 
    
    declare @tsql nvarchar(1000)
    declare @loopstart int
    declare @loopend int
    declare @email nvarchar(400)
    
    set @loopstart = (Select min(ID) from EmailList)
    set @loopend = (Select max(ID) from EmailList)
    
    Create table tempFinalList
    (
    [Path] nvarchar(2000),
    [EmailId] nvarchar(400)
    )
    
    While (@loopstart < = @loopend)
    Begin
    set @email = (Select EmailID from EmailList where ID = @loopstart)
    
    Set @tsql = 'select distinct a.Path , ' + @email + ' as [email]
    from Catalog as a
    inner join Subscriptions  as b on b.Report_OID=a.ItemID
    where ExtensionSettings like %' + @email + '%'
    
    Insert Into tempFinalList
    EXEC(@tsql)
    
    Set @loopstart = @loopstart + 1
    
    End
    


    Step 5: Final Select the output:

    Select * from tempFinalList

    Thanks,


    Kindly mark the reply as answer if they help

    Wednesday, January 4, 2017 1:04 PM
  • Thank you much for the helpful response.

    I get the below errors though on step 4:

    Please assist

    Many thanks

    Wednesday, January 4, 2017 11:14 PM
  • changing the like statement slightly, also gives errors as per the below:

    Please assist

    Regards

    Wednesday, January 4, 2017 11:25 PM
  • Hi Zimiso,

    According to your description, it seems that you would like to find out the subscription e-mails address and responding reports which exist in your list, right?

    As Sunil mentioned, first of all we are able to import List of emails to a table, for example the list query like:

    select Email from EmailList

    then try to use the query like below to accomplish the requirement:

    /****** Script for SelectTopNRows command from SSMS  ******/
    Use ReportServer
    SELECT 
      y.SubscriberList, 
      y.ReportPath 
    FROM (   
      SELECT  
        PseudoTable.TheseNodes.value('(./Value)[1]', 'varchar(MAX)') AS SubscriberList, 
        x.ReportPath 
         
        FROM (    
          SELECT  
            sub.Description AS Recipients, 
            CAST(sub.ExtensionSettings AS xml) AS Subscribers, 
            cat.[Path] AS ReportPath 
          FROM 
            dbo.Subscriptions sub 
            JOIN dbo.[Catalog] AS cat ON 
              sub.Report_OID = cat.ItemID 
    		
        ) x 
          CROSS APPLY Subscribers.nodes('/ParameterValues/ParameterValue') AS PseudoTable(TheseNodes) 
      WHERE 
        PseudoTable.TheseNodes.value('(./Name)[1]', 'varchar(100)') = 'TO' 
      ) y 
    WHERE 
     exists (select Email from EmailList)
    ORDER BY 
      SubscriberList, 
      ReportPath

    For example, the results of select Email from EmailList is pirlozh@xxx.com, execute the query above:

    If I misunderstand your requirement, please correct me.

    Thanks,

    Pirlo Zhang


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, January 5, 2017 3:20 AM
  • Hi Pirlo,

    I received the below, which works perfectly for me:

    --Create and populate a Numbers table CREATE TABLE dbo.Numbers (Number int PRIMARY KEY) INSERT INTO dbo.Numbers (Number) SELECT TOP 5000 ROW_NUMBER() OVER (ORDER BY c1.object_id) FROM sys.columns c1 JOIN sys.columns c2 ON 1=1 GO --Create a user-defined function to parse delimited strings into rows CREATE FUNCTION dbo.udftGetParsedValues( @InputString varchar(MAX), @Delimiter char(1) ) RETURNS @tvValues TABLE ( [Value] varchar(MAX), [Index] int) AS BEGIN IF @Delimiter <> ' ' BEGIN SET @Delimiter = NULLIF(@Delimiter, '') END --Remove trailng delimiters WHILE RIGHT(@InputString,1) = @Delimiter BEGIN SET @InputString = LEFT(@InputString, LEN(@InputString + 'x') - 1 - 1) END INSERT INTO @tvValues ([Value], [Index]) SELECT SUBSTRING( @Delimiter + @InputString + @Delimiter, N.Number + 1, CHARINDEX( @Delimiter, @Delimiter + @InputString + @Delimiter, N.Number + 1 ) - N.Number - 1 ), ROW_NUMBER() OVER (ORDER BY N.Number) FROM dbo.Numbers N WHERE SUBSTRING( @Delimiter + @InputString + @Delimiter, N.Number, 1 ) = @Delimiter AND N.Number < (LEN( @Delimiter + @InputString + @Delimiter + 'x' ) - 1) RETURN END GO --Query to retrieve all subscribers to all SSRS report, listing each subscriber --on a separate row SELECT distinct LTRIM(RTRIM(pv.Value)) AS Subscriber, y.ReportPath FROM ( SELECT PseudoTable.TheseNodes.value('(./Value)[1]', 'varchar(MAX)') AS SubscriberList, x.ReportPath FROM ( SELECT sub.Description AS Recipients, CAST(sub.ExtensionSettings AS xml) AS Subscribers, cat.[Path] AS ReportPath FROM dbo.Subscriptions sub JOIN dbo.[Catalog] AS cat ON sub.Report_OID = cat.ItemID ) x CROSS APPLY Subscribers.nodes('/ParameterValues/ParameterValue') AS PseudoTable(TheseNodes) WHERE PseudoTable.TheseNodes.value('(./Name)[1]', 'varchar(100)') = 'TO' ) y CROSS APPLY dbo.udftGetParsedValues(y.SubscriberList, ';') pv ORDER BY Subscriber, ReportPath

    Just for interests sake of it, do you perhaps know how I can the resolve the errors I received with my previous approach?

    Many thanks,

    Regards

    Thursday, January 5, 2017 4:54 AM
  • Hi Zimiso,

    Here is the updated one and It should work fine.

    declare @tsql nvarchar(1000)
    declare @loopstart int
    declare @loopend int
    declare @email nvarchar(400)
    
    set @loopstart = (Select min(ID) from EmailList)
    set @loopend = (Select max(ID) from EmailList)
    
    Create table tempFinalList
    (
    [Path] nvarchar(2000),
    [EmailId] nvarchar(400)
    )
    
    While (@loopstart < = @loopend)
    Begin
    set @email = (Select EmailID from EmailList where ID = @loopstart)
    
    Set @tsql = ' select distinct a.Path , '+ '''' + @email + ''''+ ' as [email]
    from Catalog as a
    inner join Subscriptions  as b on b.Report_OID=a.ItemID
    where ExtensionSettings like ' + ''''+ '%'  + @email + '%' + ''''
    
    Insert Into tempFinalList
    EXEC(@tsql)
    
    Set @loopstart = @loopstart + 1
    
    End


    Kindly mark the reply as answer if they help

    • Marked as answer by RonTech1 Thursday, January 5, 2017 5:05 AM
    Thursday, January 5, 2017 4:56 AM
  • Hi Sunil,

    Thank you much; this works perfectly!

    Thursday, January 5, 2017 5:05 AM