{"id":305,"date":"2012-07-13T16:17:13","date_gmt":"2012-07-13T21:17:13","guid":{"rendered":"http:\/\/www.cognitiveinfo.com\/?p=305"},"modified":"2024-06-26T01:24:41","modified_gmt":"2024-06-26T06:24:41","slug":"bulk-update-ssrs-subscription","status":"publish","type":"post","link":"http:\/\/cognitiveinfo.com\/?p=305","title":{"rendered":"Bulk Update SSRS Subscription Information"},"content":{"rendered":"

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.\u00a0 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.<\/p>\n

Necessary Precautions:<\/h2>\n

Messing around in the Report Server database can hose your system. So take these steps first:
\n1. Make a backup of your ReportServer_SSRS database and ReportServer_SSRSTemp.
\n2. Test the Backup.
\n3. Try this on a development server first. If you are looking for bracelet. There’s something to suit every look, from body-hugging to structured, from cuffs to chain chain bracelet<\/a> and cuffs.<\/p>\n

List the SSRS Subscriptions<\/h2>\n

To view all the subscriptions, it is as easy as running a quick query:
\n
\nUSE ReportServer_SSRS
\nGO
\nSELECT
\nc.Name AS ReportName
\n, c.Type
\n, c.Description AS ReportDescription
\n, u.UserName AS ReportCreatedBy
\n, s.Description AS SubscriptionDescription
\n, s.DeliveryExtension AS SubscriptionDelivery
\n, su.UserName AS SubscriptionOwner
\n, s.LastStatus
\n, s.LastRunTime
\n, s.Parameters
\n,sch.StartDate AS ScheduleStarted
\n,sch.LastRunTime AS LastSubRun
\n, sch.NextRunTime
\n, d.Name AS DataSource
\n, c.Path
\nFROM
\nCatalog c
\nLEFT OUTER JOIN\u00a0 Subscriptions s ON c.ItemID = s.Report_OID
\nINNER JOIN\u00a0 DataSource d ON c.ItemID = d.ItemID
\nLEFT OUTER JOIN\u00a0 Users u ON u.UserID = c.CreatedByID
\nLEFT OUTER JOIN ReportSchedule rs ON c.ItemID = rs.ReportID
\nLEFT OUTER JOIN\u00a0 Schedule sch ON rs.ScheduleID = sch.ScheduleID
\nLEFT OUTER JOIN Users su on s.ownerID = su.UserID
\nWHERE
\nc.Type = 2
\nand s.SubscriptionID is not null
\nORDER BY c.Name<\/tt><\/p>\n

Once we had this list, we were able to change the where clause to narrow it down based on delivery methods.<\/p>\n

Update SSRS Subscription Owner<\/h2>\n

If you need to update the Owner of the subscriptions, there is a good blog article here<\/a> by Jeremiah Clark.\u00a0 His query, shown below got me working on other methods to help replace the server file share path:
\n
\nUSE Reports_SSRS
\nGO
\nDECLARE @OldUserID uniqueidentifier
\nDECLARE @NewUserID uniqueidentifier
\nSELECT @OldUserID = UserID FROM dbo.Users WHERE UserName = 'DOMAINA\\OldUser'
\nSELECT @NewUserID = UserID FROM dbo.Users WHERE UserName = 'DOMAINB\\NewUser'
\nUPDATE dbo.Subscriptions SET OwnerID = @NewUserID WHERE OwnerID = @OldUserID
\n<\/tt><\/p>\n

Now that the ownership has been changed, it is an easy query to update the fileshare paths:
\n
\nUPDATE dbo.Subscriptions
\nSET
\nExtensionSettings =
\nREPLACE(CONVERT(VARCHAR(max),ExtensionSettings )
\n,'\\\\OldFileShareServer\\NewPath\\'
\n, '\\\\NewFileShareServer\\NewPath\\')
\nWHERE DeliveryExtension = 'Report Server FileShare'
\n<\/tt><\/p>\n","protected":false},"excerpt":{"rendered":"

Some tips for bulk updating subscriptions in SQL Server Reporting Services 2008 R2.<\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[8,15],"tags":[64,79,102],"class_list":["post-305","post","type-post","status-publish","format-standard","hentry","category-microsoftsql","category-ssrs","tag-microsoft-sql","tag-reporting-services","tag-ssrs"],"_links":{"self":[{"href":"http:\/\/cognitiveinfo.com\/index.php?rest_route=\/wp\/v2\/posts\/305","targetHints":{"allow":["GET"]}}],"collection":[{"href":"http:\/\/cognitiveinfo.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/cognitiveinfo.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/cognitiveinfo.com\/index.php?rest_route=\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"http:\/\/cognitiveinfo.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=305"}],"version-history":[{"count":1,"href":"http:\/\/cognitiveinfo.com\/index.php?rest_route=\/wp\/v2\/posts\/305\/revisions"}],"predecessor-version":[{"id":1232,"href":"http:\/\/cognitiveinfo.com\/index.php?rest_route=\/wp\/v2\/posts\/305\/revisions\/1232"}],"wp:attachment":[{"href":"http:\/\/cognitiveinfo.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=305"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/cognitiveinfo.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=305"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/cognitiveinfo.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=305"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}