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

SharePoint Integrated Mode - Where can I find what reports are associated with which shared schedule(s)? Expand / Collapse
Author
Message
Posted Thursday, December 12, 2013 2:44 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, December 22, 2014 8:35 PM
Points: 39, Visits: 151
One would think this is fairly simple. All I want is to find out in SharePoint running SSRS 2008 R2 (Integrated Mode) what reports are associated with which shared schedules?

I have searched this forum SQL Server Central, and Google; till I am literally nauseated and ready to throw my monitor out of a window. All I can find is queries like I have at the bottom; or instructions (not helpful at all btw) on how to view where the shared schedules are; or get the Guid from the Sql Server Agent Job Activity Monitor and helplessly match it up with the schedule on the "Manage Shared Schedules" screen. However, NONE of these options tell me WHAT report is on WHICH schedule. SharePoint Integrated mode has been such a pain in the general gluteaus maximus that I will NEVER recommend it to ANYONE ever and this is just one of many simple tasks that take an entire afternoon when dealing with more than a dozen reports.

In the SSRS Native Mode, all you do is go to Site Settings > Schedules click on a schedule and then click Reports and TAH DAH a freakin list. Please someone save my sanity and tell me how to do this in Integrated Mode, so I can get us back to Native and leave this backwards, overly complicated, poorly designed, poorly documented, poorly executed piece of flotsam off my network.


Thanks :)



USE
ReportServer
SELECT
Schedule.ScheduleID AS SQLAgent_Job_Name, Subscriptions.Description AS sub_desc, Subscriptions.DeliveryExtension AS sub_delExt,
[Catalog].Name AS ReportName, [Catalog].Path AS ReportPath, StartDate,NextRunTime,Schedule.LastRunTime,EndDate,Schedule.EventType,ExtensionSettings,Subscriptions.Description,LastStatus

FROM ReportSchedule
INNER JOIN Schedule
ON ReportSchedule.ScheduleID = Schedule.ScheduleID
INNER JOIN Subscriptions
ON ReportSchedule.SubscriptionID = Subscriptions.SubscriptionID
INNER JOIN [Catalog]
ON ReportSchedule.ReportID = [Catalog].ItemID AND Subscriptions.Report_OID = [Catalog].ItemID


WHERE
ExtensionSettings
Like '%blahblahblah%' --use to find who is on the to to,cc,bcc field settings

ORDER
BY

Schedule.LastRunTime DESC


USE
ReportServer


SELECT
Schedule.ScheduleID AS SQLAgent_Job_Name,
Subscriptions.Description AS sub_desc,
Subscriptions.DeliveryExtension AS sub_delExt,
[Catalog].Name AS ReportName,
[Catalog].Path AS ReportPath,
Schedule.LastRunTime AS LastRun


FROM ReportSchedule

INNER JOIN Schedule
ON
ReportSchedule.ScheduleID = Schedule.ScheduleID


INNER JOIN Subscriptions
ON
ReportSchedule.SubscriptionID = Subscriptions.SubscriptionID


INNER JOIN [Catalog]
ON
ReportSchedule.ReportID = [Catalog].ItemID
AND
Subscriptions.Report_OID = [Catalog].ItemID


WHERE
Subscriptions.DeliveryExtension = 'Report Server Email'

ORDER
BY
Schedule.LastRunTime DESC
Post #1522491
Posted Friday, December 13, 2013 8:53 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, December 22, 2014 8:35 PM
Points: 39, Visits: 151
Major props go out to Trevor Seward on TechNet for providing a way to see all the subscriptions via a PowerShell Script.

http://social.technet.microsoft.com/Forums/sharepoint/en-US/6950236a-61a4-4430-bc1c-ec4a50555e03/where-can-i-find-what-reports-are-associated-with-which-shared-schedules?forum=sharepointadminprevious#6950236a-61a4-4430-bc1c-ec4a50555e03

Seriously,
Thanks Trevor

Post #1522735
Posted Friday, December 13, 2013 8:54 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, December 22, 2014 8:35 PM
Points: 39, Visits: 151
Trevor's Script:

$siteUri = "[yourSharepointSiteHere]"

$proxy = New-WebServiceProxy -Uri "$siteUri/_vti_bin/ReportServer/ReportService2010.asmx" -UseDefaultCredential -Namespace "SSRS"
$proxy.ListSubscriptions($siteUri) | Export-CSV -NoType -Path c:\ReportsSubs_List.csv


Trevor's blog post to get more info:
http://sharepoint.nauplius.net/2013/12/quickly-identifying-reporting-services-subscriptions/
Post #1522737
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse