I’ve recut a lot of article on the subject to build this receipe , it’s quite simple once you know the trick :

1. Run the following query to get the “Report Path” from the list of all the schedule

SELECT SUB.ModifiedDate
,SUB.[Description] , SUB.SubscriptionID , rs.ScheduleID
,SUB.DeliveryExtension ,SUB.LastStatus
,SUB.LastRunTime ,SCH.NextRunTime
,SCH.Name AS ScheduleName ,CAT.[Path] AS ReportPath
,CAT.[Description] AS ReportDescription
FROM dbo.Subscriptions AS SUB
INNER JOIN dbo.Users AS USR ON SUB.OwnerID = USR.UserID
INNER JOIN dbo.[Catalog] AS CAT ON SUB.Report_OID = CAT.ItemID
INNER JOIN dbo.ReportSchedule AS RS ON SUB.Report_OID = RS.ReportID
AND SUB.SubscriptionID = RS.SubscriptionID
INNER JOIN dbo.Schedule AS SCH ON RS.ScheduleID = SCH.ScheduleID

2. After that pick the same query and filter by “Report Path” to get the wanted “subscriptionID”


SELECT SUB.ModifiedDate
,SUB.[Description] , SUB.SubscriptionID , rs.ScheduleID
,SUB.DeliveryExtension ,SUB.LastStatus
,SUB.LastRunTime ,SCH.NextRunTime
,SCH.Name AS ScheduleName ,CAT.[Path] AS ReportPath
,CAT.[Description] AS ReportDescription
FROM dbo.Subscriptions AS SUB
INNER JOIN dbo.Users AS USR ON SUB.OwnerID = USR.UserID
INNER JOIN dbo.[Catalog] AS CAT ON SUB.Report_OID = CAT.ItemID
INNER JOIN dbo.ReportSchedule AS RS ON SUB.Report_OID = RS.ReportID
AND SUB.SubscriptionID = RS.SubscriptionID
INNER JOIN dbo.Schedule AS SCH ON RS.ScheduleID = SCH.ScheduleID

where CAT.[Path] like '/MyFirstReport/Report1'

3. Next trick is an excel spread sheet and paste all the “SubscriptionID” in the first collomn and the following string in the second “EXEC dbo.AddEvent @EventType=’TimedSubscription’, @EventData =”

4. In the third collomn just use the excel function “Concatenate” to get the complete string

=CONCATENER(B1 & "'" & A1 &"'")

5. Copy paste the result in a query in your ReportServer database and run it ,

Saved me a lot of headache 🙂

Ref : http://www.randallkent.com/2011/10/06/trigger-ssrs-subscription-manually/ for the execute part , thanks dude !

 

Comments are closed.