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,[ExtensionSett ings]).val ue('(//Par ameterValu e/Value[.. /Name="TO" ])[1]','nv archar(max )') as [To]
,Convert(XML,[ExtensionSet tings]).va lue('(//Pa rameterVal ue/Value[. ./Name="CC "])[1]','n varchar(ma x)') as [CC]
,Convert(XML,[ExtensionSet tings]).va lue('(//Pa rameterVal ue/Value[. ./Name="BC C"])[1]',' nvarchar(m ax)') as [BCC]
,Convert(XML,[ExtensionSet tings]).va lue('(//Pa rameterVal ue/Value[. ./Name="Re plyTo"])[1 ]','nvarch ar(max)') as [ReplyTo]
,Convert(XML,[ExtensionSet tings]).va lue('(//Pa rameterVal ue/Value[. ./Name="Su bject"])[1 ]','nvarch ar(max)') as [Subject]
from Subscriptions
Looking for help on the code to update these individual fields
Thanks!
I have the following query to create the data dump:
SELECT SubscriptionID, extensionSettings,
Convert(XML,[ExtensionSett
,Convert(XML,[ExtensionSet
,Convert(XML,[ExtensionSet
,Convert(XML,[ExtensionSet
,Convert(XML,[ExtensionSet
from Subscriptions
Looking for help on the code to update these individual fields
Thanks!
ASKER
Would something like this do the trick?
Update Subscriptions
set ExtensionSettings=CAST(REP LACE(CAST( extensionS ettings AS XML).value('(//ParameterVa lue/Value[ ../Name="T O"])[1]',' nvarchar(m ax)'),'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
Update Subscriptions
set ExtensionSettings=CAST(REP
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Pro SQL Server 2008 Reporting Services
By Rodney Landrum, Shawn McGehee, Walter J. Voytek, II
http://books.google.com/books?id=myWloEn3f58C&pg=PA263&lpg=PA263&dq=how+to+update+ExtensionSettings+values+within+the+Subscriptions+ssrs+2008&source=bl&ots=Onxo7sr7dW&sig=bchhC8B2sylpybvnKdUMtMcKFOE&hl=en&sa=X&ei=4q0YT-bKJun00gHV872VCw&ved=0CCcQ6AEwAQ#v=onepage&q=how%20to%20update%20ExtensionSettings%20values%20within%20the%20Subscriptions%20ssrs%202008&f=false