Change SSRS subscription save path in bulk

Question

We are about to change one of the file servers and will be renaming it to something else. We have thousands of SSRS subscriptions saving reports to this server so I need to find a way of mass changing the destination server of where the reports end up. I've gone through the ReportServer tables and can't find anything obvious where i could do an UPDATE statement to change the destination server.

Any assistance would be greatly appreciated.

I feel i may have asked a question that will have fallen on death ears so let me explore my own findings. It appears under the Subscriptions table there is a field called ExtensionSettings, I'm confident this is linked to it — rmon2852, Apr 15 '16 at 07:39
Ok it was a bold one, but I backed up and did a giant REPLACE(@OLDserver, @newserver) update on the Extensions table — rmon2852, Apr 15 '16 at 08:13

score 1 · Accepted Answer · answered Apr 15 '16 at 07:53

1

SELECT ExtensionSettings FROM [ReportServer].[dbo].[Subscriptions]

will give you the current paths. Backup first!

answered Apr 15 '16 at 07:53

BIDeveloper

2,628
4
36
51

score 0 · Answer 2 · answered Apr 16 '16 at 07:39

For anyone wanting to know the exact code in the future:

UPDATE dbo.Subscriptions
SET ExtensionSettings=REPLACE(convert(varchar(max),ExtensionSettings),'OLDSERVER','NEWSERVER') 
where ExtensionSettings LIKE '%OLDSERVER%'

And may I stress........ BACKUP BEFORE THIS

Change SSRS subscription save path in bulk

2 Answers2