Friday, November 16, 2012

SSRS - Subscription comments location.

Today I was facing a client's problem in SSRS to edit the comments text for over 120 reports to point to a server name (Host Name) instead of IP, to begin with, it was a bad idea and judgment from the person who created the report. and secondly it's rarely to see a server that gets an IP change too!

But the two issues could happen and it's ok, let's face it and fix it.

Here's a screen shot, If you're not familiar with the subscription comments.


To locate it, just navigate to SQL server management studio (SSMS), and browse the :

[ReportServer].[dbo].[Subscriptions] Table

the column that has the comments is [ExtensionSettings]

just replace the text using your T-SQL query and you're done! the data type of that column in nText, so it won't with T-SQL replace function.

so you might use...



UPDATE  S
  
SET     S.ExtensionSettings =
T.ExtensionSettings
  
FROM [ReportServer].[dbo].[Subscriptions] AS
S
  
JOIN
(
  
SELECT [SubscriptionID], REPLACE(CONVERT(VARCHAR(MAX),[ExtensionSettings]),'162.162.162.162','HostNameServer')  
ExtensionSettings
  
FROM [ReportServer].[dbo].[Subscriptions]) AS
T
  
ON S.SubscriptionID  = T.[SubscriptionID]

 


Hope that helps someone...
Jason


Code has been formatted using...http://extras.sqlservercentral.com/prettifier/prettifier.aspx