I have a current client who needed to update information in every SSRS Subscription on their production server. The main reason was they were moving domains. This meant updating user names and pointing the subscriptions to a new file share server where it saves reports. This is a standalone instance of SQL Server 2008 R2 Reporting Services, not in SharePoint integrated mode. It finally dawned on me that there is no bulk management tool for SSRS reports or subscriptions. So until I get time to learn VB.net and build a tool we needed a quick way to view and edit all of these subscription properties.
Necessary Precautions:
Messing around in the Report Server database can hose your system. So take these steps first:
1. Make a backup of your ReportServer_SSRS database and ReportServer_SSRSTemp.
2. Test the Backup.
3. Try this on a development server first.
List the SSRS Subscriptions
To view all the subscriptions, it is as easy as running a quick query:
USE ReportServer_SSRS
GO
SELECT
c.Name AS ReportName
, c.Type
, c.Description AS ReportDescription
, u.UserName AS ReportCreatedBy
, s.Description AS SubscriptionDescription
, s.DeliveryExtension AS SubscriptionDelivery
, su.UserName AS SubscriptionOwner
, s.LastStatus
, s.LastRunTime
, s.Parameters
,sch.StartDate AS ScheduleStarted
,sch.LastRunTime AS LastSubRun
, sch.NextRunTime
, d.Name AS DataSource
, c.Path
FROM
Catalog c
LEFT OUTER JOIN Subscriptions s ON c.ItemID = s.Report_OID
INNER JOIN DataSource d ON c.ItemID = d.ItemID
LEFT OUTER JOIN Users u ON u.UserID = c.CreatedByID
LEFT OUTER JOIN ReportSchedule rs ON c.ItemID = rs.ReportID
LEFT OUTER JOIN Schedule sch ON rs.ScheduleID = sch.ScheduleID
LEFT OUTER JOIN Users su on s.ownerID = su.UserID
WHERE
c.Type = 2
and s.SubscriptionID is not null
ORDER BY c.Name
Once we had this list, we were able to change the where clause to narrow it down based on delivery methods.
Update SSRS Subscription Owner
If you need to update the Owner of the subscriptions, there is a good blog article here by Jeremiah Clark. His query, shown below got me working on other methods to help replace the server file share path:
USE Reports_SSRS
GO
DECLARE @OldUserID uniqueidentifier
DECLARE @NewUserID uniqueidentifier
SELECT @OldUserID = UserID FROM dbo.Users WHERE UserName = 'DOMAINA\OldUser'
SELECT @NewUserID = UserID FROM dbo.Users WHERE UserName = 'DOMAINB\NewUser'
UPDATE dbo.Subscriptions SET OwnerID = @NewUserID WHERE OwnerID = @OldUserID
Now that the ownership has been changed, it is an easy query to update the fileshare paths:
UPDATE dbo.Subscriptions
SET
ExtensionSettings =
REPLACE(CONVERT(VARCHAR(max),ExtensionSettings )
,'\\OldFileShareServer\NewPath\'
, '\\NewFileShareServer\NewPath\')
WHERE DeliveryExtension = 'Report Server FileShare'