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

CREATEPROCEDURE usp_UpdateOldEmailAddress

(

@OldEmailAddressVARCHAR(100)

, @NewEmailAddressVARCHAR(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

BEGINTRANSACTION

UPDATE Subscriptions

SET ExtensionSettings =CONVERT(NTEXT,REPLACE(CONVERT(VARCHAR(MAX),ExtensionSettings),@OldEmailAddress,@NewEmailAddress))

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