Link to home
Start Free TrialLog in
Avatar of dhalliday
dhalliday

asked on

How do i modify specific ExtensionSettings details in the Subscriptions for SSRS

I am trying to write a script to update the individual fields of the ExtensionSettings values within the Subscriptions dbo of SSRS.

I have the following query to create the data dump:

SELECT SubscriptionID, extensionSettings,
Convert(XML,[ExtensionSettings]).value('(//ParameterValue/Value[../Name="TO"])[1]','nvarchar(max)') as [To]
,Convert(XML,[ExtensionSettings]).value('(//ParameterValue/Value[../Name="CC"])[1]','nvarchar(max)') as [CC]
,Convert(XML,[ExtensionSettings]).value('(//ParameterValue/Value[../Name="BCC"])[1]','nvarchar(max)') as [BCC]
,Convert(XML,[ExtensionSettings]).value('(//ParameterValue/Value[../Name="ReplyTo"])[1]','nvarchar(max)') as [ReplyTo]
,Convert(XML,[ExtensionSettings]).value('(//ParameterValue/Value[../Name="Subject"])[1]','nvarchar(max)') as [Subject]
from Subscriptions

Looking for help on the code to update these individual fields

Thanks!

Avatar of EugeneZ
EugeneZ
Flag of United States of America image

Avatar of dhalliday
dhalliday

ASKER

Would something like this do the trick?

Update Subscriptions
set ExtensionSettings=CAST(REPLACE(CAST(extensionSettings AS XML).value('(//ParameterValue/Value[../Name="TO"])[1]','nvarchar(max)'),'xx','xxx')AS XML)
where subscriptionid='ID'

or am i missing something in the chain?  Still learning a lot of this on the fly, so I am not too familiar with the correct syntax.

Thanks
ASKER CERTIFIED SOLUTION
Avatar of EugeneZ
EugeneZ
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial