Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Query to get list of susbscriptions with params and values Expand / Collapse
Author
Message
Posted Wednesday, September 12, 2012 6:03 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, September 19, 2014 11:51 AM
Points: 37, Visits: 146
I've be searching and trying for hours to get a query that gives me all current subscriptions from and SSRS serv with parameters and their values, so that they can be recreated on a new server (after pruning)

For example a simple report might be HoursByDepartment that takes three params:

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

What I want to get is something along these lines (or something that will let me 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'm pretty good with XSl, so if i could get something like, 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>


Regards

Mark
Post #1358274
Posted Thursday, September 13, 2012 1:56 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Monday, December 8, 2014 1:45 AM
Points: 5,221, Visits: 5,120
This should get you to a pretty good starting point
SELECT
U.UserName AS SubscriptionOwner,
C.Path AS ReportPath,
CONVERT(XML,S.ExtensionSettings) AS SubscriptionSettings,
S.EventType AS SubscriptionType,
CONVERT(XML,S.Parameters) AS ReportParameters,
S.DeliveryExtension AS DeliveryMethod,
Sc.Name AS ScheduleName,
Rs.ScheduleID
FROM
Subscriptions S
INNER JOIN
Users U
ON
S.OwnerID = U.UserID
INNER JOIN
Catalog C
ON
S.Report_OID = C.ItemID
INNER JOIN
ReportSchedule RS
ON
S.SubscriptionID = RS.SubscriptionID
INNER JOIN
Schedule Sc
ON
RS.ScheduleID = Sc.ScheduleID
ORDER BY
1,2





Want an answer fast? Try here
How to post data/code for the best help - Jeff Moden
Need a string splitter, try this - Jeff Moden
How to post performance problems - Gail Shaw
CrossTabs-Part1 & Part2 - Jeff Moden
SQL Server Backup, Integrity Check, and Index and Statistics Maintenance - Ola Hallengren
Managing Transaction Logs - Gail Shaw
Troubleshooting SQL Server: A Guide for the Accidental DBA - Jonathan Kehayias and Ted Krueger

Post #1358376
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse