Tuesday, July 5, 2011

View Reporting Services subscriptions and run them ad hoc

In some Reporting Services environments, subscriptions are extensively used to distribute business data. This results in 10's or even 100's or subscriptions. Its quite useful to have a view of these subscriptions and the associated sql agent jobs that actually run these. The thing is these jobs have GUID's as names. Very human unfriendly. There are many reasons I'd want to see this, for example, whne last was a subscription run? One of the main reasons I'd want to see a job linked to a subscription is if I wanted to run a subscription manually. I could of course edit the schedule of the subscription, or create a once off schedule, but that is quite tedious.

I've written some code to 1st of all view Subscriptions in a friendly way. This selects from 3 tables in the ReportServer database and shows the subscriptions and the associated job name. This is the data I display in my 1st Reporting Services report, i.e. show me all subscriptions and the associated job names, and some other data.

I then have a 2nd reporting services report to actually run the SQL agent job that runs the subscription and show the results of the job execution. So I'm using a RS report to execute a SQL agent job. From the 1st report I pass the job_name value to the 2nd report as an input parameter. This parameter is used to start a sql agent job, using the system stored procedure sp_start_job. I then wait for 30 seconds and get the last execution value of that job from sysjobhistory.

Herewith the code:
Select to show subscriptions and populate 1st report:

c.Name AS ReportName
, rs.ScheduleID AS JOB_NAME
, s.[Description]
, s.LastStatus
, s.LastRunTime
ReportServer..[Catalog] c
JOIN ReportServer..Subscriptions s ON c.ItemID = s.Report_OID
JOIN ReportServer..ReportSchedule rs ON c.ItemID = rs.ReportID
AND rs.SubscriptionID = s.SubscriptionID
order by LastRunTime desc

Code to run report subscription based on job_name:

create proc usp_sub @JobName NVarchar(255)

exec msdb.dbo.sp_start_job @JobName
WAITFOR DELAY '00:00:30'

select top 1 message, run_date, run_time, run_duration from msdb.dbo.sysjobhistory jh
inner join msdb.dbo.sysjobs j
on jh.job_id = j.job_id
where j.name = @JobName
and step_id = 0
order by jh.instance_id desc

The RS reports are very basic, just displaying the results of the above queries in tables. On the main report that shows the subscriptions, I have an action defined on the Subscription name column. The action is to go to the 2nd report which runs the job, taking the job_name (GUID) as an input parameter for the above stored proc.