Were are the email addresses stored for SSRS Subscriptions ? Also how can I modify them via code

• mw_sql_developer

  SSCoach

  Points: 19445

  More actions

  October 23, 2018 at 1:20 pm

  #380329

  The following code is the furthest I went on digging in to Report Server. Can anyone help me to find the exact place  where the email address list for a subscription is stored.
  When I find that i have to do a bulk update on certain email addresses.

     Select Su.Description
    FROM dbo.Schedule s
        INNER JOIN ReportSchedule RS
            ON S.ScheduleID = RS.ScheduleID
        INNER JOIN Catalog Ca
            ON Ca.ItemID = RS.ReportID
        INNER JOIN Subscriptions Su
            ON Su.SubscriptionID = RS.SubscriptionID
        INNER JOIN Users U
            ON U.UserID = S.CreatedById
            OR U.UserID = Su.OwnerID

• mw_sql_developer

  SSCoach

  Points: 19445

  More actions

  October 23, 2018 at 1:29 pm

  #2010842

  mw_sql_developer - Tuesday, October 23, 2018 1:20 PM

  The following code is the furthest I went on digging in to Report Server. Can anyone help me to find the exact place  where the email address list for a subscription is stored.
  When I find that i have to do a bulk update on certain email addresses.

     Select Su.Description
    FROM dbo.Schedule s
        INNER JOIN ReportSchedule RS
            ON S.ScheduleID = RS.ScheduleID
        INNER JOIN Catalog Ca
            ON Ca.ItemID = RS.ReportID
        INNER JOIN Subscriptions Su
            ON Su.SubscriptionID = RS.SubscriptionID
        INNER JOIN Users U
            ON U.UserID = S.CreatedById
            OR U.UserID = Su.OwnerID

  I am lost .. Can someone modify the code below (  I found this in the internet ) 

  
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
y.SubscriberList IS NOT NULL
ORDER BY
SubscriberList,
ReportPath

• mw_sql_developer

  SSCoach

  Points: 19445

  More actions

  October 23, 2018 at 1:39 pm

  #2010844

  mw_sql_developer - Tuesday, October 23, 2018 1:29 PM

  mw_sql_developer - Tuesday, October 23, 2018 1:20 PM

  The following code is the furthest I went on digging in to Report Server. Can anyone help me to find the exact place  where the email address list for a subscription is stored.
  When I find that i have to do a bulk update on certain email addresses.

     Select Su.Description
    FROM dbo.Schedule s
        INNER JOIN ReportSchedule RS
            ON S.ScheduleID = RS.ScheduleID
        INNER JOIN Catalog Ca
            ON Ca.ItemID = RS.ReportID
        INNER JOIN Subscriptions Su
            ON Su.SubscriptionID = RS.SubscriptionID
        INNER JOIN Users U
            ON U.UserID = S.CreatedById
            OR U.UserID = Su.OwnerID

  I am lost .. Can someone modify the code below (  I found this in the internet ) 

  
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
y.SubscriberList IS NOT NULL
ORDER BY
SubscriberList,
ReportPath

  Never mind.... Finally found the place....

  Select top 100 SubscriptionID, sub.ExtensionSettings, CAST(sub.ExtensionSettings AS xml) , *
FROM
dbo.Subscriptions sub

• mw_sql_developer

  SSCoach

  Points: 19445

  More actions

  October 23, 2018 at 2:03 pm

  #2010848

  mw_sql_developer - Tuesday, October 23, 2018 1:39 PM

  mw_sql_developer - Tuesday, October 23, 2018 1:29 PM

  mw_sql_developer - Tuesday, October 23, 2018 1:20 PM

  The following code is the furthest I went on digging in to Report Server. Can anyone help me to find the exact place  where the email address list for a subscription is stored.
  When I find that i have to do a bulk update on certain email addresses.

     Select Su.Description
    FROM dbo.Schedule s
        INNER JOIN ReportSchedule RS
            ON S.ScheduleID = RS.ScheduleID
        INNER JOIN Catalog Ca
            ON Ca.ItemID = RS.ReportID
        INNER JOIN Subscriptions Su
            ON Su.SubscriptionID = RS.SubscriptionID
        INNER JOIN Users U
            ON U.UserID = S.CreatedById
            OR U.UserID = Su.OwnerID

  I am lost .. Can someone modify the code below (  I found this in the internet ) 

  
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
y.SubscriberList IS NOT NULL
ORDER BY
SubscriberList,
ReportPath

  Never mind.... Finally found the place....

  Select top 100 SubscriptionID, sub.ExtensionSettings, CAST(sub.ExtensionSettings AS xml) , *
FROM
dbo.Subscriptions sub

  Well then my next question is where are the email addresses that go on the CC list stored in the database. Certainly it does not look like the table dbo.Subscriptions
  Well then what other table can it be ?

• mw_sql_developer

  SSCoach

  Points: 19445

  More actions

  October 23, 2018 at 2:05 pm

  #2010849

  mw_sql_developer - Tuesday, October 23, 2018 2:03 PM

  mw_sql_developer - Tuesday, October 23, 2018 1:39 PM

  mw_sql_developer - Tuesday, October 23, 2018 1:29 PM

  mw_sql_developer - Tuesday, October 23, 2018 1:20 PM

  The following code is the furthest I went on digging in to Report Server. Can anyone help me to find the exact place  where the email address list for a subscription is stored.
  When I find that i have to do a bulk update on certain email addresses.

     Select Su.Description
    FROM dbo.Schedule s
        INNER JOIN ReportSchedule RS
            ON S.ScheduleID = RS.ScheduleID
        INNER JOIN Catalog Ca
            ON Ca.ItemID = RS.ReportID
        INNER JOIN Subscriptions Su
            ON Su.SubscriptionID = RS.SubscriptionID
        INNER JOIN Users U
            ON U.UserID = S.CreatedById
            OR U.UserID = Su.OwnerID

  I am lost .. Can someone modify the code below (  I found this in the internet ) 

  
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
y.SubscriberList IS NOT NULL
ORDER BY
SubscriberList,
ReportPath

  Never mind.... Finally found the place....

  Select top 100 SubscriptionID, sub.ExtensionSettings, CAST(sub.ExtensionSettings AS xml) , *
FROM
dbo.Subscriptions sub

  Well then my next question is where are the email addresses that go on the CC list stored in the database. Certainly it does not look like the table dbo.Subscriptions
  Well then what other table can it be ?

  BTW -- Updating email address is easy .. The code below works.. BUT MY QUESTION is I cannot find where those email addresses that go on the CC list are saved in the DB

  
Update dbo.Subscriptions
Set ExtensionSettings = REPLACE( CAST( ExtensionSettings as VARCHAR(MAX) ) , '@cat.com', '@rat.org')
WHERE
SubscriptionID = '6675A63C-6CA2-4F88-9C6D-011A849A96D8'

• Sue_H

  SSC Guru

  Points: 90860

  More actions

  October 23, 2018 at 2:43 pm

  #2010854

  mw_sql_developer - Tuesday, October 23, 2018 2:05 PM

  mw_sql_developer - Tuesday, October 23, 2018 2:03 PM

  mw_sql_developer - Tuesday, October 23, 2018 1:39 PM

  mw_sql_developer - Tuesday, October 23, 2018 1:29 PM

  mw_sql_developer - Tuesday, October 23, 2018 1:20 PM

  The following code is the furthest I went on digging in to Report Server. Can anyone help me to find the exact place  where the email address list for a subscription is stored.
  When I find that i have to do a bulk update on certain email addresses.

     Select Su.Description
    FROM dbo.Schedule s
        INNER JOIN ReportSchedule RS
            ON S.ScheduleID = RS.ScheduleID
        INNER JOIN Catalog Ca
            ON Ca.ItemID = RS.ReportID
        INNER JOIN Subscriptions Su
            ON Su.SubscriptionID = RS.SubscriptionID
        INNER JOIN Users U
            ON U.UserID = S.CreatedById
            OR U.UserID = Su.OwnerID

  I am lost .. Can someone modify the code below (  I found this in the internet ) 

  
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
y.SubscriberList IS NOT NULL
ORDER BY
SubscriberList,
ReportPath

  Never mind.... Finally found the place....

  Select top 100 SubscriptionID, sub.ExtensionSettings, CAST(sub.ExtensionSettings AS xml) , *
FROM
dbo.Subscriptions sub

  Well then my next question is where are the email addresses that go on the CC list stored in the database. Certainly it does not look like the table dbo.Subscriptions
  Well then what other table can it be ?

  BTW -- Updating email address is easy .. The code below works.. BUT MY QUESTION is I cannot find where those email addresses that go on the CC list are saved in the DB

  
Update dbo.Subscriptions
Set ExtensionSettings = REPLACE( CAST( ExtensionSettings as VARCHAR(MAX) ) , '@cat.com', '@rat.org')
WHERE
SubscriptionID = '6675A63C-6CA2-4F88-9C6D-011A849A96D8'

  If it's a data driven subscription, those are setup when the subscription is created. Edit the subscription and check the query used for the email addresses.

  Sue

• mw_sql_developer

  SSCoach

  Points: 19445

  More actions

  October 24, 2018 at 7:27 am

  #2010920

  Sue_H - Tuesday, October 23, 2018 2:43 PM

  mw_sql_developer - Tuesday, October 23, 2018 2:05 PM

  mw_sql_developer - Tuesday, October 23, 2018 2:03 PM

  mw_sql_developer - Tuesday, October 23, 2018 1:39 PM

  mw_sql_developer - Tuesday, October 23, 2018 1:29 PM

  mw_sql_developer - Tuesday, October 23, 2018 1:20 PM

  The following code is the furthest I went on digging in to Report Server. Can anyone help me to find the exact place  where the email address list for a subscription is stored.
  When I find that i have to do a bulk update on certain email addresses.

     Select Su.Description
    FROM dbo.Schedule s
        INNER JOIN ReportSchedule RS
            ON S.ScheduleID = RS.ScheduleID
        INNER JOIN Catalog Ca
            ON Ca.ItemID = RS.ReportID
        INNER JOIN Subscriptions Su
            ON Su.SubscriptionID = RS.SubscriptionID
        INNER JOIN Users U
            ON U.UserID = S.CreatedById
            OR U.UserID = Su.OwnerID

  I am lost .. Can someone modify the code below (  I found this in the internet ) 

  
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
y.SubscriberList IS NOT NULL
ORDER BY
SubscriberList,
ReportPath

  Never mind.... Finally found the place....

  Select top 100 SubscriptionID, sub.ExtensionSettings, CAST(sub.ExtensionSettings AS xml) , *
FROM
dbo.Subscriptions sub

  Well then my next question is where are the email addresses that go on the CC list stored in the database. Certainly it does not look like the table dbo.Subscriptions
  Well then what other table can it be ?

  BTW -- Updating email address is easy .. The code below works.. BUT MY QUESTION is I cannot find where those email addresses that go on the CC list are saved in the DB

  
Update dbo.Subscriptions
Set ExtensionSettings = REPLACE( CAST( ExtensionSettings as VARCHAR(MAX) ) , '@cat.com', '@rat.org')
WHERE
SubscriptionID = '6675A63C-6CA2-4F88-9C6D-011A849A96D8'

  If it's a data driven subscription, those are setup when the subscription is created. Edit the subscription and check the query used for the email addresses.

  Sue

  Sorry, I checked it is the same field, There is a section in the XML [<Name>CC</Name><Value>] and following that yo have the email addresses that go in the CC Section. 

  So lets close this thread here.  Thanks for all the help
  So the answer is to check the   ExtensionSettings field/.