Solved

SQl server 2005, reporting services, change extentions field in subscriptions table

Posted on 2007-04-05
12
311 Views
Last Modified: 2008-01-09
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.
0
Comment
Question by:Fraser_Admin
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 6
12 Comments
 
LVL 15

Expert Comment

by:Megan Brooks
ID: 18860607
You might be able to do this using a data driven subscription, especially if you can generate the correct list of email addresses for each copy of the report using an SQL query. The output of the query can both supply the reply-to list and supply the same value to a report parameter, if necessary, so that both values match.

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.
0
 

Author Comment

by:Fraser_Admin
ID: 18861619
I don't have the ability to do data driven subscriptions since I don't have enterprise edition.

0
 
LVL 15

Expert Comment

by:Megan Brooks
ID: 18867776
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/ParameterValue[Name="ReplyTo"]/Value)[1]', 'varchar(255)'
    ) AS ReplyTo,
    CONVERT(xml, Parameters ).value(
        '(/ParameterValues/ParameterValue[Name="ShowProgress"]/Value)[1]', 'varchar(255)')
    AS ShowProgress
FROM         Subscriptions
WHERE     (NOT (CONVERT(xml, ExtensionSettings ).value('(/ParameterValues/ParameterValue[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-E3D5D3D34445'

SELECT @ReplyTo
SET @ReplyTo.modify('replace value of (/ParameterValues/ParameterValue[Name="ReplyTo"]/Value/text())[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.ExtensionSettings.

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.
0
Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

 

Author Comment

by:Fraser_Admin
ID: 18876420
yea, i would like to be able to edit those xml values if possible.  do you have any idea how to do this?
0
 
LVL 15

Expert Comment

by:Megan Brooks
ID: 18876624
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...
0
 

Author Comment

by:Fraser_Admin
ID: 18877288
thanks that would be great.  
0
 
LVL 15

Accepted Solution

by:
Megan Brooks earned 500 total points
ID: 18880261
Here is T-SQL that modifies ReplyTo in the Subscriptions table:

declare      @id uniqueidentifier,
      @tp binary(16),
      @es xml,
      @esl int,
      @params xml,
      @oldReplyTo nvarchar(4000),
      @paramVal nvarchar(4000),
      @newEs nvarchar(4000)

declare ChangeReplyTo cursor for
SELECT TEXTPTR(ExtensionSettings), ExtensionSettings, datalength(ExtensionSettings), Parameters
    FROM Subscriptions WHERE (DataSettings IS NULL)

open ChangeReplyTo
fetch next from ChangeReplyTo into @tp, @es, @esl, @params

while @@fetch_status = 0
    begin
    set @oldReplyTo = @es.value(
        '(/ParameterValues/ParameterValue[Name="ReplyTo"]/Value)[1]', 'nvarchar(4000)')

    set @paramVal = @params.value(
        '(/ParameterValues/ParameterValue[Name="ShowProgress"]/Value)[1]', 'nvarchar(4000)')

    if @esl < 7800 AND NOT @paramVal IS NULL
        begin
        print @oldReplyTo
        print @paramVal
        set @es.modify('replace value of (/ParameterValues/ParameterValue[Name="ReplyTo"]/Value/text())[1] with "somebody@somewhere.com" ')
        set @newEs = N'WRITETEXT dbo.Subscriptions.ExtensionSettings @tp N''' + CONVERT(nvarchar(4000), @es) + N''''
        exec sp_executesql @newEs, N'@tp binary(16)', @tp
        end

    fetch next from ChangeReplyTo into @tp, @es, @esl, @params
    end

close ChangeReplyTo
deallocate ChangeReplyTo

Whether it is a good idea to do this or not depends on how 'private' the report server is. If it is used only for a specific application, this solution should be OK. If it is used by many different people for many different things, it may not be.

The two main things to be concerned about are selecting the right subscription records and keeping the length of ExtensionSettings under 4000 characters for those subscriptions that must be modified. This example ignores subscriptions if ExtensionSettings is not less than 3900 characters (7800 bytes), to leave a little room for the ReplyTo field to grow and to leave room for the rest of the WRITETEXT. It also skips records that don't have a particular parameter name (ShowProgress). You can modify the SQL to select the parameters you need, and to further restrict which subscriptions are modified.

The length of ExtensionSettings can become a problem if the subscription contains long strings (TO string, Comment string, etc.). The approach shown here is limited to 4000 characters for the argument to sp_executesql. Slightly more could be squeezed out by using EXECUTE instead, but it would be more work to format the TEXTPTR value. The length check could be refined. It might also be possible to do a partial write back to ExtensionSettings, overwriting just the ReplyTo value, but this could be quite a bit of extra work and would be even more kludgey, and I didn't want to attempt it.

There might be a better way to do this -- I HOPE there is -- but this is what came to my mind, and it worked when I tried it. It would not be this messy if Report Server were not using ntext columns, which are deprecated in SQL Server 2005, to store the XML fragments.
0
 

Author Comment

by:Fraser_Admin
ID: 18897986
ok i will give it a try and let you know.  thanks
0
 

Author Comment

by:Fraser_Admin
ID: 18898052
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?
0
 
LVL 15

Assisted Solution

by:Megan Brooks
Megan Brooks earned 500 total points
ID: 18899438
To obtain information about the report itself, as opposed to the subscription, join Subscriptions to _ 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.

For example,
SELECT     Catalog.Path, Catalog.Name, Subscriptions.Description
FROM         Subscriptions INNER JOIN
                      Catalog ON Subscriptions.Report_OID = Catalog.ItemID

That's good if you only need to change one report; you are unlikely to encounter problems with things changing unexpectedly, and you will probably be able to ensure that the ExtensionSettings column doesn't grow too large.

I won't repeat the entire example I gave earlier but the SELECT query in the DECLARE CURSOR now becomes something like
SELECT TEXTPTR(S.ExtensionSettings), S.ExtensionSettings,
      datalength(S.ExtensionSettings), S.Parameters
FROM Subscriptions S INNER JOIN Catalog C ON
      S.Report_OID = C.ItemID
WHERE (S.DataSettings IS NULL) AND (C.Name LIKE 'NameOrPattern')

You can substitute either a single report name or a pattern for 'NameOrPattern'.

0
 
LVL 15

Expert Comment

by:Megan Brooks
ID: 18900639
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."
0
 

Author Comment

by:Fraser_Admin
ID: 18918013
works perfect.  thanks
0

Featured Post

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
SQL Server 2012 to SQL Server 2016 24 54
store vs query adhoc - no show rows 4 37
When are cursors useful? 8 58
SQL Server 2012 and core licensing 5 29
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

739 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question