Manually run a SSRS subscription
I came up with an issue today where I wanted to script out running a email subscription that didn’t fit into the standard occurrence where the normal scheduling system would work.
I wanted to send the report when ever I wanted by using preferably T-SQL. The solution it turns out its is pretty easy. Subscriptions are basically Agent Jobs. If you can find out the Agent jobs name (subscriptions Jobs use a GUID for there name!)
Below is a break down of how to do it.
Set up a subscription as you normally would for the report. But when setting the schedule set it to run once at a time In the passed (This will stop it from sending out the email on a pre-defined schedule)
Now we just need to query the server to find track the subscription to the Agent Job.
(I use a query I found on MSSQLTIPS.com, http://www.mssqltips.com/sqlservertip/2742/sql-server-reporting-services-2008-r2-report-server-database-helpful-queries/)
Now we just throw in the JobID to sp_start_job
/* Run Subscription to SSRS -- Use Subscription Report.sql to find the job_id */ -- Run Agent Job EXEC msdb.dbo.sp_start_job @job_name = '6061197C-6F3D-4689-8F35-B43998659DB6'
And there you have it. A Report you can email on demand 🙂