Creating subscriptions in SQL Server Reporting Services is a great way to distribute files to end users. If it has been a while since you have begun using subscriptions and you have a bunch of them out there, there will more than likely come a time when at least one of those subscribers has left the company. Either by choice or by being kicked out on their face. Well, maybe not on their face right? J Or perhaps maybe your company decided to change their domain name and no longer will accept the @olddomainname.com email addresses.

At any rate, so now you have this user who has been getting some of the great reports that your company has developed emailed to him/her. With the employee being gone and a person dedicated to receiving those reports, you now need a way to update the email address inside all of those subscriptions without going into each subscription and updating them. Below is a stored procedure that I have created to do just that. It takes two parameters: the @OldEmailAddress and the @NewEmailAddress respectively.

USE ReportServer

GO

CREATE PROCEDURE usp_UpdateOldEmailAddress

(

      @OldEmailAddress      VARCHAR(100)

      , @NewEmailAddress    VARCHAR(100)

/*************************************************************

Original Author:        Brian K. McDonald, MCDBA, MCSD

Original Creation Date: 7/18/2010

Purpose:

      To update the ReportServer.dbo.Subscriptions table with a

      new subscribers email address. This could be used when an

      employee who receives emailed documents no longer is

      employed by the company and you need it to be sent to their

      replacement. Or, perhaps maybe the company has changed their

      domain name and will no longer forward the old domain.

Sample Execution:

EXECUTE usp_UpdateOldEmailAddress

      @OldEmailAddress = 'BrianKMcDonald@MyEmailAddress.com'

      , @NewEmailAddress = 'BMcDonald@MyNewEmailAddress.com'

**************************************************************/

)

AS

--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

NOTE: As a word of caution though, I personally would make a backup of the ReportServer database and/or the Subscription table before making massive updates like this. Of course, one should probably have a maintenance plan that backs the Reporting Services database nightly anyways, but that is another story. J

Until next time, “keep your ear to the grindstone” – Good Will Hunting

Brian K. McDonald, MCDBA, MCSD
Business Intelligence Consultant – Pragmatic Works Consulting

Email: bmcdonald@pragmaticworks.com | Blog: BI Developer Network

Convert with DTS xChange  | Develop with BI xPress  | Process with TaskFactory | Document with BI Documenter