SSRS Subscription Changes - Update emails/track changes

• SQL Wayne

  Say Hey Kid

  Points: 709

  More actions

  September 23, 2015 at 10:26 am

  #320854

  I have a list of emails for employees that are no longer with the company. I need to remove them from all SSRS subscriptions.

  I found this query online which I am using. However, I am wondering if there is a more automated way to do this and/or is there a way to track what changes I have made?

  Here is the query I am using:

  DECLARE @Subscriptions TABLE (

  Report_OID UNIQUEIDENTIFIER,

  ToList VARCHAR(8000),

  CCList VARCHAR(8000),

  BCCList VARCHAR(8000),

  SubjectLine VARCHAR(8000))DECLARE @ExtensionSettings XML

  DECLARE @Report_OID UNIQUEIDENTIFIER

  DECLARE @idoc INT

  DECLARE SubscriptionList CURSOR FOR

  SELECT Report_OID,ExtensionSettings

  FROM subscriptions

  OPEN SubscriptionList

  FETCH NEXT FROM SubscriptionList INTO @Report_OID ,@ExtensionSettings

  WHILE (@@FETCH_STATUS=0)

  BEGIN

  EXEC sp_xml_preparedocument @idoc OUTPUT, @ExtensionSettings

  INSERT INTO @Subscriptions

  SELECT @Report_OID,[TO],[CC],[BCC],[Subject]

  FROM

  (

  SELECT *

  FROM OPENXML (@idoc, '/ParameterValues/ParameterValue')

  WITH (Name NVARCHAR(100) 'Name',

  Value NVARCHAR(100) 'Value')

  ) AS SourceTable

  pivot

  (

  MAX(value) FOR [Name] IN ([TO],[BCC],[CC],[Subject])

  ) AS pivottable

  EXEC sp_xml_removedocument @idoc

  FETCH NEXT FROM SubscriptionList INTO @Report_OID ,@ExtensionSettings

  END

  CLOSE SubscriptionList

  DEALLOCATE SubscriptionList

  SELECT distinct c.path,c.name,s.Tolist,s.cclist,s.bcclist,s.subjectline

  FROM Catalog c

  INNER JOIN @Subscriptions s ON c.ItemID = s.Report_OID

  Where s.ToList like '%Aaron.peterson%'