2

Can existing SSRS Subscriptions be amended using T-SQL? I have a scenario where many subscriptions failed to send due to a server error. I want to move all of them to a new date/time, and afterwards move them all back again.

opperman.eric
  • 314
  • 1
  • 14

2 Answers2

3

To answer your question...

Can existing SSRS Subscriptions be amended using T-SQL?

I am not sure if you can change the subscription with TSQL. I have extracted the subscriptions with SQL... see this answer I posted with SQL query: is there a way to query future SSRS subscription schedules?.

I have been playing around with PowerShell a lot recently for Administative automation scripting. There is a ReportingServicesTools module you can install for PowerShell from Microsoft on Github. I would recommend that you try learning that. It looks cool in my opinion, and so far with my testing it seems to have some promising capabilities.

NOTE: I have Windows 7 which comes with Powershell Version 2. I had to upgrade my Powershell to Version 4 to install the RS module using these instructions: 1) MS Installing Windows PowerShell, 2) How to check the PowerShell version & install a new version. To check your version of Powershell execute either of these commands... $PSVersionTable.PSVersion or $psversiontable, as seen in this post: Determine installed PowerShell version.

I want to move all of them to a new date/time, and afterwards move them all back again.

The purpose of PowerShell is not only to view objects, but that you can also modify them.

The RSTools posh module has quite a few relevant cmdlets including: Get-RsSubscripton, Set-RsSubscripton, Copy-RsSubscripton, Export-RsSubscripton, Import-RsSubscripton, New-RsSubscripton, Remove-RsSubscripton. Below I have demoed the Help and the Subscription Properties on the server based a on single folder using GET and SET.

The Set-RsSubscription cmdlet has parameters to let you easily change the subscription properties StartDateTime, EndDate, Owner. And then there is the parameter SubProperties, not sure at this point what this one is.

DEMO: Powershell Code

Declare variables 

$reportServerUri_src = "http://gcod049/ReportServer"
$reportServerUri_tgt = "http://gcod050/ReportServer"

Find The Subscriptions (one folder returns 2 subscriptions.. 'd8e0decf-86f3-49cb-896f-3af644be1be3' and '4c1539c4-9e0f-42c4-aace-b493c96ec2e4')

Get-RsSubscription -ReportServerUri $reportServerUri_src -RsItem "/fsqa"

Change the STARTDATETIME For A Specific Subscription

Get-RsSubscription -ReportServerUri $reportServerUri_src -Path "/fsqa" | Where-Object {$_.SubscriptionId -eq '4c1539c4-9e0f-42c4-aace-b493c96ec2e4'} | Set-RsSubscription -StartDateTime "2/13/2019 1:20pm" -verbose

Change the STARTDATETIME For An Entire Folder

Get-RsSubscription -ReportServerUri $reportServerUri_src -Path "/fsqa" |  Set-RsSubscription -StartDateTime "2/13/2019 1:20pm" -verbose

(BONUS) Move all subscriptions from one server folder to another (gcod050 "/fsqa" to gcod049 "/fsqa")

--Move Subscriptions (From/To Folder)

NOTE, 2/13/19 : A bug is being worked out on this right now. 

Get-RsSubscription -ReportServerUri $reportServerUri_src -RsItem "/fsqa" | Copy-RsSubscription -ReportServerUri $reportServerUri_tgt -RsItem "/fsqa" -verbose

--Create Folder

New-RsFolder -ReportServerUri $reportServerUri_tgt -RsFolder '/' -FolderName 'FSQA' -Description 'Reports for Food Saftey Quality Asurance department' -Verbose

--- Messages output from '-Verbose' parameter ---
VERBOSE: Establishing proxy connection to http://gcod050/ReportServer/ReportService2010.asmx...
VERBOSE: Creating folder FSQA...
VERBOSE: Folder FSQA created successfully!

--Move Reports (From/To Folder)

Well... I couldn't quite figure out the PS module command to move a report From Server-A to Server-B. The commands to published reports via Powershell all appear to work with local files (Link Name: Article). Here is an easy-to-use tool you can download for moving reports from ServerA to ServerB, SSRS 2008 R2 to SSRS 2016 Migration. 

NOTE:
The "Content" cmdlets (based upon the documentation) appear to move files from/to a directory, instead of ServerA to ServerB

CMD:
get-command -module ReportingServicesTools *cont*

OUTPUT:
CommandType     Name                                               ModuleName
-----------     ----                                               ----------
Function        Get-RsFolderContent                                ReportingServicesTools
Function        Out-RsFolderContent                                ReportingServicesTools
Function        Out-RsRestFolderContent                            ReportingServicesTools
Function        Write-RsFolderContent                              ReportingServicesTools
Function        Write-RsRestFolderContent                          ReportingServicesTools

DEMO: Screenshot

POSH_Get-RsSubscription -ReportServerUri $reportServerUri -Path fsqa.png

Get-RsSubscription -ReportServerUri $reportServerUri -Path "/fsqa"

enter image description here

POSH_get-help get-RsSubscription.png

get-help Get-RsSubscription

enter image description here

POSH_Get-Help Set-RsSubscription -detailed

Get-Help Set-RsSubscription -detailed

enter image description here

POSH_Get-Help Copy-RsSubscription.png

Get-Help Copy-RsSubscription

enter image description here

POSH_Get-Command -Module ReportingServicesTools.png

Get-Command -Module ReportingServicesTools

enter image description here

UPDATE Tue 02/12/2019 16:27:33.22

I found another article from AUG 2017 which talks about how to modify the SSRS Subscription programatically also via PowerShell-- Warrenestes.com "Update Multiple SSRS Subscriptions". On his site he has developed his own PoSH functions using New-WebServiceProxy, which looked a bit complicated to me. But he did say this in his article about cmdlet SET-RSSUBSCRIPTION: 

Edit:  This is now part of the official Microsoft ReportingServicesTools PowerShell module. 
This a brand new function, which was recently renamed from Update to Set-RsSubscription!!!!!.

and he said this ...

I did find a post on dba.stackexchange referencing the real [EndDate] from a column 
in **dbo.Subscritions** called… wait for the descriptive name... **[MatchData]**!
SherlockSpreadsheets
  • 2,062
  • 3
  • 27
  • 47
2

When I have issues with reports not running, I usually refire the subscription using EXEC dbo.AddEvent with the subscription ID. 

SELECT e.name
   , e.path
   , d.description
   , a.SubscriptionID
   , laststatus
   , LastRunTime
   , date_modified
   , 'EXEC dbo.AddEvent @EventType = ''TimedSubscription'', @EventData = '' ' + CAST(a.SubscriptionID AS VARCHAR(100)) + ''''
FROM 
ReportServer.dbo.ReportSchedule a 
JOIN msdb.dbo.sysjobs b ON CAST(a.ScheduleID AS NVARCHAR(200)) = b.name
JOIN ReportServer.dbo.Subscriptions d  ON a.SubscriptionID = d.SubscriptionID
JOIN ReportServer.dbo.Catalog e ON d.report_oid = e.itemid
WHERE d.LastStatus LIKE 'Fail%'
    AND eventtype = 'TimedSubscription'
ORDER BY e.name
Hannover Fist
  • 10,393
  • 1
  • 18
  • 39