Solved

sql server 2005 reporting services description displayed on subscriptions

Posted on 2007-04-04
12
589 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
[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
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 16

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 16

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

 

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 16

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 16

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

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

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

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Viewers will learn how the fundamental information of how to create a table.

690 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