May 28, 2012 at 1:45 am
as you already have the subscription setup, I would remove the schedule from the T-SQL job which is used to run the subscription, then as step 2 of your load from oracle, issue a SP_START_JOB procedure call to run the subscription job.
the script below should help you find the right GUID job name for the report.
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
November 12, 2013 at 1:17 pm
http://www.bidn.com/blogs/kylewalker/ssis/1915/how-to-match-your-subcription-names-with-its-job-id
Some things I've learned researching this topic in sql 2008 r2.
1) ssrs scheduled tasks create a corresponding sqlagent job with a nasty guid name that is meaningless
2) the bidn link above by Mr. Kyle Walker shows the query to retrieve and map the sqlagent guid name to your scheduled ssrs report.
3) putting all this information together with the msdb.dbo.sp_start_job, you can manage your scheduled reports within sql agent jobs with much flexibility.
I'm not sure I'm going to like managing this set up though during deployment of a new ssrs report? We'll see.
Viewing 2 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply