Solved

Change subscription report delivery to

Posted on 2009-07-06
3
553 Views
Last Modified: 2012-05-07
We're running SQL Server Reporting Services 2005.

We've replaced an employee so they need to get all the subscriptions of their predecessor.  Is there SQL I can run that will change the delivery recipients on all the subscriptions?

Ideally I'd run something like:
Update Subscriptions
Set DeliveryTo = 'Tom';'Jane'
Where DeliveryTo = 'Tom';'Sue'

Thanks,
 TT
0
Comment
Question by:TommyTupa
[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
  • 2
3 Comments
 
LVL 11

Expert Comment

by:Andrei Teodorescu
ID: 24791997
update data in Subscriptions table (ReportServer database), [ExtensionSettings] column
here is an example:

<ParameterValues>
<ParameterValue><Name>TO</Name><Value>00225930</Value></ParameterValue>
<ParameterValue><Name>IncludeReport</Name><Value>True</Value></ParameterValue>
<ParameterValue><Name>RenderFormat</Name><Value>EXCEL</Value></ParameterValue>
<ParameterValue><Name>Subject</Name><Value>@ReportName was executed at @ExecutionTime</Value>
</ParameterValue><ParameterValue><Name>IncludeLink</Name><Value>True</Value></ParameterValue>
<ParameterValue><Name>Priority</Name><Value>NORMAL</Value></ParameterValue>
</ParameterValues>

change here <Value>LogonName</Value>

you may want to change [OwnerID] column as well, but you have to lookup in Users table to find corresponding ID
0
 
LVL 11

Expert Comment

by:Andrei Teodorescu
ID: 24792001
sorry; so, instead:
<ParameterValue><Name>TO</Name><Value>00225930</Value></ParameterValue>
change to
<ParameterValue><Name>TO</Name><Value>LogonName</Value></ParameterValue>
0
 
LVL 13

Accepted Solution

by:
TommyTupa earned 0 total points
ID: 24820174
Ended up running this:
Update Subscriptions
Set ExtensionSettings =
replace(cast(ExtensionSettings as nvarchar(max)),'PersonX',PersonY'),
Description = Replace(Description,'PersonX','PersonY')
Where SubscriptionID in
(select
        S.[SubscriptionID]
from
    [Subscriptions] S inner join [Catalog] CAT on S.[Report_OID] = CAT.[ItemID]
    left outer join [ActiveSubscriptions] A with (NOLOCK) on S.[SubscriptionID]
= A.[SubscriptionID]
Where ExtensionSettings like '%PersonX%')
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SSRS report showing old data. 5 157
SSRS Problems 9 116
How can I setup ssrs report  as UTF-8 . 7 28
SSRS 2016 report not exporting latest data with exporting to Excel 2 35
Written by Valentino Vranken. Introduction: The first step of creating a SQL Server Reporting Services (SSRS) report involves setting up a connection to the data source and programming a dataset to retrieve data from that data source.  The data…
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

751 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