SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


SharePoint Integrated Mode - Where can I find what reports are associated with which shared...


SharePoint Integrated Mode - Where can I find what reports are associated with which shared schedule(s)?

Author
Message
asheppardwork
asheppardwork
Old Hand
Old Hand (315 reputation)Old Hand (315 reputation)Old Hand (315 reputation)Old Hand (315 reputation)Old Hand (315 reputation)Old Hand (315 reputation)Old Hand (315 reputation)Old Hand (315 reputation)

Group: General Forum Members
Points: 315 Visits: 166
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 Smile



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
asheppardwork
asheppardwork
Old Hand
Old Hand (315 reputation)Old Hand (315 reputation)Old Hand (315 reputation)Old Hand (315 reputation)Old Hand (315 reputation)Old Hand (315 reputation)Old Hand (315 reputation)Old Hand (315 reputation)

Group: General Forum Members
Points: 315 Visits: 166
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

w00t
asheppardwork
asheppardwork
Old Hand
Old Hand (315 reputation)Old Hand (315 reputation)Old Hand (315 reputation)Old Hand (315 reputation)Old Hand (315 reputation)Old Hand (315 reputation)Old Hand (315 reputation)Old Hand (315 reputation)

Group: General Forum Members
Points: 315 Visits: 166
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/
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search