Fraser_Admin
asked on
SQl server 2005, reporting services, change extentions field in subscriptions table
In ReportServer database where are my parameters held in the subscriptions table. (ie. the parameters i use to call my report).
And how could I update the ReplyTo part of the Extensions field based on the value in one of my parameters.
The reason I want to do this is because I need to change the reply to email address for all reports that are being run for a certain location, and i don't feel like editing every record, since I have a lot.
And how could I update the ReplyTo part of the Extensions field based on the value in one of my parameters.
The reason I want to do this is because I need to change the reply to email address for all reports that are being run for a certain location, and i don't feel like editing every record, since I have a lot.
ASKER
I don't have the ability to do data driven subscriptions since I don't have enterprise edition.
That does make it harder to do. The parameters are stored in the Parameters column, in an XML fragment similar to that used for ExtensionSettings (used in the solution for your other question 22492290). A given parameter can be extracted using an XPATH query, as in
SELECT CONVERT(xml, ExtensionSettings ).value(
'(/ParameterValues/Paramet erValue[Na me="ReplyT o"]/Value) [1]', 'varchar(255)'
) AS ReplyTo,
CONVERT(xml, Parameters ).value(
'(/ParameterValues/Paramet erValue[Na me="ShowPr ogress"]/V alue)[1]', 'varchar(255)')
AS ShowProgress
FROM Subscriptions
WHERE (NOT (CONVERT(xml, ExtensionSettings ).value('(/ParameterValues /Parameter Value[Name ="ReplyTo" ]/Value)[1 ]', 'varchar(255)') IS NULL))
This query finds values for the "Reply To" field and for the parameter "ShowProgress" in subscriptions for which "Reply To" is specified.
Changing ReplyTo is difficult because ExtensionSettings is defined as the depricated type 'ntext' instead of 'xml', and I don't know of a way to modify such a column directly. The only way I know of to update an ntext field is with WRITETEXT, which only accepts data from a literal or a parameter. Short of connecting from an application, I don't know how it can be changed, so that would be a question for someone more familiar with that datatype.
The XML itself is easy enough to change, though. For example,
declare @ReplyTo xml
SELECT @ReplyTo = ExtensionSettings
FROM Subscriptions
WHERE SubscriptionID = '967AEA59-3F8E-4A32-8A46-E 3D5D3D3444 5'
SELECT @ReplyTo
SET @ReplyTo.modify('replace value of (/ParameterValues/Paramete rValue[Nam e="ReplyTo "]/Value/t ext())[1] with "somebody@somewhere.com" ')
SELECT @ReplyTo
You can change the WHERE clause in the first query to anything that selects a single row. The problem now is how to get the result back into Subscriptions.ExtensionSet tings.
I suppose one other alternative would be to run a script against the web service and make the changes that way. That's not something I can put together easily right now.
SELECT CONVERT(xml, ExtensionSettings ).value(
'(/ParameterValues/Paramet
) AS ReplyTo,
CONVERT(xml, Parameters ).value(
'(/ParameterValues/Paramet
AS ShowProgress
FROM Subscriptions
WHERE (NOT (CONVERT(xml, ExtensionSettings ).value('(/ParameterValues
This query finds values for the "Reply To" field and for the parameter "ShowProgress" in subscriptions for which "Reply To" is specified.
Changing ReplyTo is difficult because ExtensionSettings is defined as the depricated type 'ntext' instead of 'xml', and I don't know of a way to modify such a column directly. The only way I know of to update an ntext field is with WRITETEXT, which only accepts data from a literal or a parameter. Short of connecting from an application, I don't know how it can be changed, so that would be a question for someone more familiar with that datatype.
The XML itself is easy enough to change, though. For example,
declare @ReplyTo xml
SELECT @ReplyTo = ExtensionSettings
FROM Subscriptions
WHERE SubscriptionID = '967AEA59-3F8E-4A32-8A46-E
SELECT @ReplyTo
SET @ReplyTo.modify('replace value of (/ParameterValues/Paramete
SELECT @ReplyTo
You can change the WHERE clause in the first query to anything that selects a single row. The problem now is how to get the result back into Subscriptions.ExtensionSet
I suppose one other alternative would be to run a script against the web service and make the changes that way. That's not something I can put together easily right now.
ASKER
yea, i would like to be able to edit those xml values if possible. do you have any idea how to do this?
As long as the ExtensionSettings column doesn't exceed 4000 characters, it should be possible to create WRITETEXT queries as a literals and execute them within a cursor loop to do what you want. The new XML data would be embedded as a literal within the literal. This is starting to look very kludgey, but I think it would work and it would be easier than writing an application or a script. I will see what I can come up with...
ASKER
thanks that would be great.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
ok i will give it a try and let you know. thanks
ASKER
OK, first thing I noticed that I forgot to mention to you is that i have several reports that have that same parameter. I would only like to change just one report. Where do I find the report name in the subscriptions table?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Oops, "_" should have been "Catalog". Lots of finger trouble today. "To obtain information about the report itself, as opposed to the subscription, join Subscriptions to Catalog using the Subscriptions.Report_OID and Catalog.ItemID. If you use a query designer, the join should be added automatically. The report name is Catalog.Name."
ASKER
works perfect. thanks
I wouldn't recommend querying the ReportServer DB directly if there is a more straightforward way to accomplish the same thing.
I am answering this in a bit of a rush. I will check back later to see if I understood, and if you still need the other information.