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)

clip_image001

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/)

which returns:

clip_image002

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 🙂

Advertisements