Updating SSRS Subscription Addresses Via TSQL

Just need to update the email addresses at the top of the script and run against the reportserver database.

/****** Script for SelectTopNRows command from SSMS  ******/
declare @OldEmailAddress varchar(1000)
declare @NewEmailAddress varchar(1000)

set @OldEmailAddress = 'Old Address'
set @NewEmailAddress = 'New Address'

--Now Update them to a new user that you want to receive the subscriptions
BEGIN TRANSACTION
UPDATE Subscriptions
SET ExtensionSettings = CONVERT(NTEXT,REPLACE(CONVERT(VARCHAR(MAX),ExtensionSettings),@OldEmailAddress,@NewEmailAddress))
FROM ReportServer.dbo.Subscriptions
WHERE CONVERT(VARCHAR(MAX),ExtensionSettings) LIKE '%' + CONVERT(VARCHAR(100),@OldEmailAddress) + '%'
COMMIT TRANSACTION

--OPTIONAL: Now just return a listing of those records that were updated
SELECT * FROM [ReportServer].[dbo].[Subscriptions]
WHERE CONVERT(VARCHAR(MAX),ExtensionSettings) LIKE '%' + CONVERT(VARCHAR(100),@NewEmailAddress) + '%'
GO

Comments

Popular posts from this blog

Monitoring Raspberry Pi Performance Using Cacti

Using NAGIOS to Check the Physical Memory Available on a Windows Host

Error 2738 Occurs When Installing An MSI Package