Solved

sql server 2005 reporting services description displayed on subscriptions

Posted on 2007-04-04
12
582 Views
Last Modified: 2009-12-16
Is there any way to change the description used for subscriptions in reporting services (sql server 2005) I would like to be able to see my comment i put in when i setup the subscription.  Since the email addy doesn't mean much to me when you are dealing with several suppliers.
0
Comment
Question by:Fraser_Admin
12 Comments
 
LVL 21

Expert Comment

by:Yurich
ID: 18853197
No, you can't change your description, the only descriptive things of your subscription you can change is a subject and commetns, but you won't see them in the list of subscriptions :(... Another thing to possible have a look at is RS database, where you can change subscription name, but I wouldn't recommend doing it from the back end.

Cheers,
Yurich
0
 
LVL 14

Expert Comment

by:Megan Brooks
ID: 18853842
Report Manager allows you to edit the description of a data driven subscription, so one option would be to use data driven subscriptions.

Otherwise, if you connect to Reporting Services using Management Studio, you can create a script for the subscription. You can then change the description in that script and then run it using the RS utility.

I would be inclined to just change it in the Description column of the Subscriptions table in the ReportServer database using an UPDATE query, rather than go to the trouble of creating, editing, and running scripts. The end result ought to be exactly the same, and you could could create the description from the comment, which you should be able to find buried in the ExtensionSettings column. You might need to take precautions to ensure that only certain subscriptions (fitting some pattern that you determine) were modified this way, rather than changing every description in the table.
0
 
LVL 14

Expert Comment

by:Megan Brooks
ID: 18854547
Apart from updating the ReportServer database directly, you could write a script to scan for reports having subscriptions, matching whatever criteria you wish, and to update the descriptions as needed. The script produced by Management Studio could serve as a starting point.
0
Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

 

Author Comment

by:Fraser_Admin
ID: 18857522
What are the downfalls to just updating the ReportServer database subscriptions table directly.  I just tried one record and it gives me the result I'm looking for, but you guys say that isn't a good idea to do.  Just wondering why?
0
 

Author Comment

by:Fraser_Admin
ID: 18857581
i guess one downfall is that it resets it back if you update the subscription.
0
 

Author Comment

by:Fraser_Admin
ID: 18857622
which table is the comment field stored in?
0
 
LVL 14

Expert Comment

by:Megan Brooks
ID: 18857849
If you are updating the subscription frequently, you could have the query or script for changing the descriptions run on a regular basis. With caution, the update might also be done using a trigger.

The comments are in the Subscriptions table, embedded in the ExtensionSettings column. This column is an XML fragment, and you would have to do something such as use string functions to locate the portion of the data that represents the comment text (a function could make this easier). If the comment contains characters that must be escaped in XML then the comment string will also have to be unescapted.
0
 

Author Comment

by:Fraser_Admin
ID: 18857882
could you post some code on how to do that?
0
 
LVL 14

Accepted Solution

by:
Megan Brooks earned 500 total points
ID: 18858921
You can extract the comment from the XML fragment using a query such as
SELECT CONVERT(xml, ExtensionSettings ).value(
    '(/ParameterValues/ParameterValue[Name="Comment"]/Value)[1]', 'varchar(60)'
) AS Comment
FROM Subscriptions

The update query would be straighforward, and would depend upon how you wanted to filter it, and how you wanted to construct the new description string from the comment.It could look something like
UPDATE    Subscriptions
SET Description = ES.Comment
FROM Subscriptions INNER JOIN
    (SELECT     SubscriptionID, CONVERT(xml, ExtensionSettings ).value'(
     /ParameterValues/ParameterValue[Name="Comment"]/Value)[1]',
     'varchar(60)') AS Comment
     FROM          Subscriptions AS Subscriptions_1) AS ES ON
        Subscriptions.SubscriptionID = ES.SubscriptionID
WHERE (Subscriptions.DataSettings IS NULL) AND
    (Subscriptions.Description LIKE N'Send e-mail to %') AND
    (NOT (ES.Comment IS NULL))
 
This query only updates non-data-driven subscription descriptions, and only if the description appears to be a default description and a comment field is defined. The comment is copied directly to the description. Despending on how you use the report server, you might need a more strict filter.
0
 

Author Comment

by:Fraser_Admin
ID: 18860163
worked perfect.  had to change it to this to work for an automatic job.

SET QUOTED_IDENTIFIER  OFF

UPDATE    Subscriptions
SET Description =
 (SELECT     CONVERT(xml, z.ExtensionSettings ).value('(/ParameterValues/ParameterValue[Name="Comment"]/Value)[1]', 'varchar(60)') AS Comment
  FROM         Subscriptions z
  where subscriptions.subscriptionid = z.subscriptionid)
Where
Subscriptions.Description LIKE N'Send e-mail to %'

SET QUOTED_IDENTIFIER  ON
0
 

Author Comment

by:Fraser_Admin
ID: 18860209
I posted another question for the second part, can you move to it???
22494982
0
 

Expert Comment

by:underhillm
ID: 23438284
You can solve the problem of the description being reset every time you update a subscription by commenting out the Description field in the UpdateSubscriptions stored procedure, as below:

update Subscriptions set
        [DeliveryExtension] = @DeliveryExtension,
        [Locale] = @Locale,
        [OwnerID] = @OwnerID,
        [InactiveFlags] = @InactiveFlags,
        [ExtensionSettings] = @ExtensionSettings,
        [ModifiedByID] = @ModifiedByID,
        [ModifiedDate] = @ModifiedDate,
        --[Description] = @Description,
        [LastStatus] = @LastStatus,
        [EventType] = @EventType,
        [MatchData] = @MatchData,
        [Parameters] = @Parameters,
        [DataSettings] = @DataSettings,
    [Version] = @Version
0

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

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

I recently went through setting up a JasperReports Server using the AWS EC2 instance, and this article will cover some basic administration tasks I had to perform.
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

803 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