January 12, 2011 at 1:20 pm
Please anyone help me.
I want to resent the scheduled failed reports through script.
I have written following t-sql to get failed reports subscriptions id.
-- Check failed reports
selectSubscriptions.SubscriptionID
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
WHERESubscriptions.DeliveryExtension = 'Report Server Email'
and Subscriptions.laststatus like '%fail%'
Result will be something like below
SubscriptionId
83C05E60-9731-4876-B7BC-057B350FBEB4
90B4B019-683A-4181-96E6-0C838281CDA8
6AA06249-7E66-4851-83A1-0CF8A97F0B21
12237FC1-0997-4E0E-BCA7-10A429D24641
624A2EEB-7A3B-4924-8C76-1BEAD398AF3B
FF02F429-E238-480E-8B81-1FA6D1D198C7
Now i need to find a way to construct following statement dynamically based on the subscriptions id above.
I want to put this all in a stored procedure so that reports can be resent in one click.
exec ReportServer.dbo.AddEvent @EventType='TimedSubscription', @EventData='83C05E60-9731-4876-B7BC-057B350FBEB'
exec ReportServer.dbo.AddEvent @EventType='TimedSubscription', @EventData='90B4B019-683A-4181-96E6-0C838281CDA8'
So basically want to know how to pass multiple values which came from temp table in eventdata variable of AddEvent stored procedure.
Thanks for your help.
January 12, 2011 at 1:27 pm
Add the command as a string to the select statement, and then execute it dynamically. Or change the select to a cursor and execute the procedure once per value in that.
select 'exec ReportServer.dbo.AddEvent @EventType=''TimedSubscription'', @EventData=''' + Subscriptions.SubscriptionID + '''"
FROM...
or
declare Events cursor static for
SELECT ...
open Events;
fetch ...
All the usual cursor code there.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 12, 2011 at 2:04 pm
Thanks for your help.
January 13, 2011 at 7:10 am
You're welcome.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply