May 11, 2011 at 10:37 pm
Hi,
I know this is pretty much simple but I just wanted to know how to manually execute a data driven/standard subscription in SSRS 2008?
I know for SSRS 2005 you are just gonna right click on the subscription and then click on execute.
Thanks!
May 12, 2011 at 6:58 am
All report subscriptions are SQL Server Agent jobs. Find the job in SSMS, right click it and choose "Start Job at Step".
Unfortunately all report subscriptions jobs have a cryptic name so the hard part is figuring out which job to run. This script should help, run it against your reporting services database:
SELECT cat.[Name]
,cat.[Path]
,res.ScheduleID AS JobID
,sub.LastRuntime
,sub.LastStatus
,sch.next_run_date
,sch.next_run_time
,LEFT(CAST(sch.next_run_date AS CHAR(8)),4)
+ '-' + SUBSTRING(CAST(sch.next_run_date AS CHAR(8)),5,2)
+ '-' + RIGHT(CAST(sch.next_run_date AS CHAR(8)),2)
+ ' ' + CASE WHEN LEN(CAST(sch.next_run_time AS VARCHAR(6))) = 5
THEN '0' + LEFT(CAST(sch.next_run_time AS VARCHAR(6)),1)
ELSE LEFT(CAST(sch.next_run_time AS VARCHAR(6)),2) END
+':' + CASE WHEN LEN(CAST(sch.next_run_time AS VARCHAR(6))) = 5
THEN SUBSTRING(CAST(sch.next_run_time AS VARCHAR(6)),2,2)
ELSE SUBSTRING(CAST(sch.next_run_time AS VARCHAR(6)),3,2) END
+ ':00.000'AS NextRunTime
,CASE WHEN job.[enabled] = 1 THEN 'Enabled' ELSE 'Disabled' END AS JobStatus
FROM dbo.Catalog AS cat
INNER JOIN dbo.Subscriptions AS sub
ON cat.ItemID = sub.Report_OID
INNER JOIN dbo.ReportSchedule AS res
ON cat.ItemID = res.ReportID
AND sub.SubscriptionID = res.SubscriptionID
INNER JOIN msdb.dbo.sysjobs AS job
ON CAST(res.ScheduleID AS VARCHAR(36)) = job.[name]
INNER JOIN msdb.dbo.sysjobschedulesAS sch
ON job.job_id = sch.job_id
ORDER BY cat.[Name]
GO
May 12, 2011 at 7:16 am
Hey nice script. I played with it and added the username of the subscription owner. Not too hard but might save some research!
SELECT
cat.[Name] AS RptName
, U.UserName
, cat.[Path]
, res.ScheduleID AS JobID
, sub.LastRuntime
, sub.LastStatus
, LEFT(CAST(sch.next_run_date AS CHAR(8)) , 4) + '-'
+ SUBSTRING(CAST(sch.next_run_date AS CHAR(8)) , 5 , 2) + '-'
+ RIGHT(CAST(sch.next_run_date AS CHAR(8)) , 2) + ' '
+ CASE WHEN LEN(CAST(sch.next_run_time AS VARCHAR(6))) = 5
THEN '0' + LEFT(CAST(sch.next_run_time AS VARCHAR(6)) , 1)
ELSE LEFT(CAST(sch.next_run_time AS VARCHAR(6)) , 2)
END + ':'
+ CASE WHEN LEN(CAST(sch.next_run_time AS VARCHAR(6))) = 5
THEN SUBSTRING(CAST(sch.next_run_time AS VARCHAR(6)) , 2 , 2)
ELSE SUBSTRING(CAST(sch.next_run_time AS VARCHAR(6)) , 3 , 2)
END + ':00.000' AS NextRunTime
, CASE WHEN job.[enabled] = 1 THEN 'Enabled'
ELSE 'Disabled'
END AS JobStatus
, sub.ModifiedDate
, sub.Description
, sub.EventType
, sub.Parameters
, sub.DeliveryExtension
, sub.Version
FROM
dbo.Catalog AS cat
INNER JOIN dbo.Subscriptions AS sub
ON cat.ItemID = sub.Report_OID
INNER JOIN dbo.ReportSchedule AS res
ON cat.ItemID = res.ReportID
AND sub.SubscriptionID = res.SubscriptionID
INNER JOIN msdb.dbo.sysjobs AS job
ON CAST(res.ScheduleID AS VARCHAR(36)) = job.[name]
INNER JOIN msdb.dbo.sysjobschedules AS sch
ON job.job_id = sch.job_id
INNER JOIN dbo.Users U
ON U.UserID = sub.OwnerID
ORDER BY
U.UserName
, RptName
May 12, 2011 at 10:16 pm
wow! great! didn't know this one. thanks a lot for the help!
May 13, 2011 at 7:13 am
Ninja's_RGR'us (5/12/2011)
Hey nice script. I played with it and added the username of the subscription owner. Not too hard but might save some research!
SELECTcat.[Name] AS RptName
, U.UserName
, cat.[Path]
, res.ScheduleID AS JobID
, sub.LastRuntime
, sub.LastStatus
, LEFT(CAST(sch.next_run_date AS CHAR(8)) , 4) + '-'
+ SUBSTRING(CAST(sch.next_run_date AS CHAR(8)) , 5 , 2) + '-'
+ RIGHT(CAST(sch.next_run_date AS CHAR(8)) , 2) + ' '
+ CASE WHEN LEN(CAST(sch.next_run_time AS VARCHAR(6))) = 5
THEN '0' + LEFT(CAST(sch.next_run_time AS VARCHAR(6)) , 1)
ELSE LEFT(CAST(sch.next_run_time AS VARCHAR(6)) , 2)
END + ':'
+ CASE WHEN LEN(CAST(sch.next_run_time AS VARCHAR(6))) = 5
THEN SUBSTRING(CAST(sch.next_run_time AS VARCHAR(6)) , 2 , 2)
ELSE SUBSTRING(CAST(sch.next_run_time AS VARCHAR(6)) , 3 , 2)
END + ':00.000' AS NextRunTime
, CASE WHEN job.[enabled] = 1 THEN 'Enabled'
ELSE 'Disabled'
END AS JobStatus
, sub.ModifiedDate
, sub.Description
, sub.EventType
, sub.Parameters
, sub.DeliveryExtension
, sub.Version
FROM
dbo.Catalog AS cat
INNER JOIN dbo.Subscriptions AS sub
ON cat.ItemID = sub.Report_OID
INNER JOIN dbo.ReportSchedule AS res
ON cat.ItemID = res.ReportID
AND sub.SubscriptionID = res.SubscriptionID
INNER JOIN msdb.dbo.sysjobs AS job
ON CAST(res.ScheduleID AS VARCHAR(36)) = job.[name]
INNER JOIN msdb.dbo.sysjobschedules AS sch
ON job.job_id = sch.job_id
INNER JOIN dbo.Users U
ON U.UserID = sub.OwnerID
ORDER BY
U.UserName
, RptName
Nice tweak to my script. We haven't let users create their own subs yet so I haven't needed this yet. But the value of the info is clear. Right into the code bank with this one. Thanks.
May 13, 2011 at 7:44 am
Ya the setup is somewhat annoying here. Users enter their own credentials (which they need to change when their password change).
So when I need to manually re-run a subscription for x report and y user I really to be able to filter the owning user :w00t:.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply