Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

sql server 2005 reporting services description displayed on subscriptions

Posted on 2007-04-04
12
Medium Priority
?
593 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 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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

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

In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

927 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