Request for a list of all SSRS subscribers with parameters

Question

I searched and tried for hours to get a request that gives me all current subscriptions from SSRS with parameters and their values ​​so that they can be recreated on a new server (after trimming).

For example, a simple report might be HoursByDepartment, which takes three parameters:

@From =Today - 7 days (Default) @To = Today (Default) @Dept = 2 (part of subscription) 

What I want to get is something in this direction (or something that will allow me to create a report)

 Report ParamName ParamValue Default HoursByDepartment From Today-7days True HoursByDepartment To Today True HoursByDepartment Dept 2 False 

OR

 Report Param1Name Param1Value Param1Def Param2Name Param2Value Param2Def HoursByDepartment From Today-7days True To Today True 

I am very good at XSl, so if I could get something, I could work with it:

 <subid> <report> <ParameterValues> <ParameterValue> <Name>MinAvailable</Name> <Value>10000</Value> </ParameterValue> <ParameterValue> <Name>OwnerIDs</Name> <Value>0</Value> </ParameterValue> <ParameterValue> <Name>ShowCosts</Name> <Value>False</Value> </ParameterValue> <ParameterValue> <Name>MinValue</Name> <Value>0</Value> </ParameterValue> </ParameterValues> </report> </subid> 

Answer 1

This script should bring you to a good start. This query returns one row for each parameter for each report subscription, or only one row for a subscription that does not use parameters. You may need to rework the script to get it in xml if you prefer to convert it.

This comes from a list of SSRS subscription reports, including parameters and their values (I don’t know how much from the original I changed if anything.)

 WITH [Sub_Parameters] AS ( SELECT [SubscriptionID], [Parameters] = CONVERT(XML,a.[Parameters]) FROM [Subscriptions] a ), [MySubscriptions] AS ( SELECT DISTINCT [SubscriptionID], [ParameterName] = QUOTENAME(p.value('(Name)[1]', 'nvarchar(max)')), [ParameterValue] = p.value('(Value)[1]', 'nvarchar(max)') FROM [Sub_Parameters] a CROSS APPLY [Parameters].nodes('/ParameterValues/ParameterValue') t(p) ), [SubscriptionsAnalysis] AS ( SELECT a.[SubscriptionID], a.[ParameterName], [ParameterValue] = (SELECT STUFF(( SELECT [ParameterValue] + ', ' as [text()] FROM [MySubscriptions] WHERE [SubscriptionID] = a.[SubscriptionID] AND [ParameterName] = a.[ParameterName] FOR XML PATH('') ),1, 0, '') +'') FROM [MySubscriptions] a GROUP BY a.[SubscriptionID],a.[ParameterName] ) SELECT a.[SubscriptionID], c.[UserName] AS Owner, b.Name, b.Path, a.[Locale], a.[InactiveFlags], d.[UserName] AS Modified_by, a.[ModifiedDate], a.[Description], a.[LastStatus], a.[EventType], a.[LastRunTime], a.[DeliveryExtension], a.[Version], e.[ParameterName], LEFT(e.[ParameterValue],LEN(e.[ParameterValue])-1) as [ParameterValue], SUBSTRING(b.PATH,2,LEN(b.PATH)-(CHARINDEX('/',REVERSE(b.PATH))+1)) AS ProjectName FROM [Subscriptions] a INNER JOIN [Catalog] AS b ON a.[Report_OID] = b.[ItemID] LEFT OUTER JOIN [Users] AS c ON a.[OwnerID] = c.[UserID] LEFT OUTER JOIN [Users] AS d ON a.MODIFIEDBYID = d.Userid LEFT OUTER JOIN [SubscriptionsAnalysis] AS e ON a.SubscriptionID = e.SubscriptionID; 

However, if this is an update from 2005 to 2008, you can consider using this tool . If you remove SSRS from this server and switch to another server using the same version, you might be better off moving all reporterver and reportservertempdb databases as explained by Microsoft here .