sql server 2005 reporting services description displayed on subscriptions

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.
Fraser_AdminAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

YurichCommented:
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
Megan BrooksSQL Server ConsultantCommented:
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
Megan BrooksSQL Server ConsultantCommented:
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
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Fraser_AdminAuthor Commented:
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
Fraser_AdminAuthor Commented:
i guess one downfall is that it resets it back if you update the subscription.
0
Fraser_AdminAuthor Commented:
which table is the comment field stored in?
0
Megan BrooksSQL Server ConsultantCommented:
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
Fraser_AdminAuthor Commented:
could you post some code on how to do that?
0
Megan BrooksSQL Server ConsultantCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Fraser_AdminAuthor Commented:
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
Fraser_AdminAuthor Commented:
I posted another question for the second part, can you move to it???
22494982
0
underhillmCommented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.