Triggering Data Driven Subscriptions in SSRS

Question

Hi

I am trying to get a data driven subscription once a set of stored procs has been run. The procs are run through SQL Agent every morning and I would like my report to run after the last of these procs has finished. I have tried using the 'When the report data is updated on the report server' option in the data driven subscription set up, but this doesn't seem to have any effect.

Is there a good way of triggering a subscription using the running of a proc? And also what exactly does the option  'When the report data is updated on the report server' mean? Is it refering to the data tables that feed the SSRS data sets?

Thanks very much

Dave

Answer 1

Although is not a supported way you could use sp_start_job to start the job that the subscription is using , (each subscription schedule creates a SQL Agent Job).

Regards

Jaime -  http://blogs.msdn.com/b/jtarquino/

This posting is provided "AS IS" with no warranties, and confers no rights

Answer 2

Hi There

Thanks for your posting. If I understand you correctly you would like to run your data driven subscription once your stored procedure has been finished.

If that is the case then I think you can use a control table (I always do for that sort of thing) and update a flag in that table after your stored procedure has been finished successfully. Say StoredprocedureRunSuccessfully is a column in your control table which got value Yes for every day and before starting your Subscription please check that status in the control table and kick in your subscription based on that.

I hope this will help

Many thanks

Syed Qazafi Anjum

Answer 3

Although is not a supported way you could use sp_start_job to start the job that the subscription is using , (each subscription schedule creates a SQL Agent Job).

Regards

Jaime -  http://blogs.msdn.com/b/jtarquino/

This posting is provided "AS IS" with no warranties, and confers no rights

Jaime,

Quick question. How to get the name of the report subscription which is available in the Sql Agent? I checked it, and happen to see, lot of alphanumeric keys in the job name. is there a way to get the unique name of that job?

---

Regards, KarthikShanth. "Mark as Answer" if this helps you!

↑ Grab this Headline Animator

Disclaimer: The contents, I write here are my personal views, not the view of my employer and anyone else.

Answer 4

Hi KarthikShanth

Thanks for your posting. I don’t know whether this is useful for you or not, but the following query will give you a relationship between MSDB and report server database about all the job/Subscription etc.

Many thanks

Syed Qazafi Anjum

Select *
FROM 
ReportServer.dbo.[Subscriptions] Subscriptions 
INNER JOIN ReportServer.dbo.ReportSchedule R ON Subscriptions.SubscriptionID = R.SubscriptionID
INNER JOIN msdb.dbo.sysjobs J ON Convert(nvarchar(128),R.ScheduleID) = J.name
INNER JOIN msdb.dbo.sysjobschedules JS ON J.job_id = JS.job_id

Answer 5

Hi Seyed

This sounds like a nice way of doing it. The problem I am having is how to automate the checking of this control table. Ideally I would like SSRS to send out the report as soon as this table has a new row inserted into it.

To give a bit more detail, I would like to have a control table with a date column and then 4 StoredProcedureRunSuccessfully columns ( I actually want to run the report after the last of four Procs has run). Every day after my Procs have run (or failed) I would like to insert a row into this table, Is it possible to have the insert into this table act as the trigger for SSRS to run the report and email it out?

Thanks very much,

Dave

Answer 6

Hi There

Thanks for your posting again.

As you might now that every Subscription we create also create a Job in Sql Agent  and information about that Job Id can be easily get using this query

Select *

FROM

ReportServer.dbo.[Subscriptions] Subscriptions

INNER JOIN ReportServer.dbo.ReportSchedule R ON Subscriptions.SubscriptionID = R.SubscriptionID

INNER JOIN msdb.dbo.sysjobs J ON Convert(nvarchar(128),R.ScheduleID) = J.name

INNER JOIN msdb.dbo.sysjobschedules JS ON J.job_id = JS.job_id

The easiest way to do this to put a last statement in your Stored procedure to kick in the Job which is associated with your Subscription so your last statement of Stored procedure might look like this

EXEC  msdb.dbo.sp_start_job  'JOB ID'

If you have any query please let me know.

I am putting a screenshot for your help

Many thanks

Syed Qazafi Anjum

Answer 7

Hi  Dave, 

To trigger or send a report subscription by using T sql is possible and is very useful.

After the procs you have in a SQL agent job have been completed you want a report subscription to be fired. Follow the steps below to do this.

1)The first step would be to create a shared schedule which is in the past
and give it a name eg My_schedule

In the reportserver database you will need to create a stored procedure which will allow you to do this but first you will need to create a Synonym to the schedule table. My understanding is that a synonym is normally used to save you time writing an object name if its on a different server, eg if you want to refer to a table called customers which was on a different server you would type 
[servername].[databasename].dbo.customers
you can create a synoynm which will allow you to refer to this object without having to type the full name

2)create synonym dbo.mycutomers for [servername].[databasename].dbo.customers

So first create synonym for the schedule table on the reportserver database

create synonym dbo.runreportschedule for [servername].[databasename].dbo.schedule

Then create a store procedure which will fire the report schedule
3)
CREATE PROC [dbo].Runreportschedule(@EventName AS NVARCHAR(1000))
as
DECLARE @myScheduleID AS NVARCHAR(1000)
SELECT

@myScheduleID=s.ScheduleID
FROM
dbo.dbo.runreportschedule AS s
WHERE
NAME = @EventName
EXEC msdb..sp_start_job @job_name =@myScheduleID


You will need to give the system prcoedure sp_start_job permissions so that it can fire the report.

Everything is now set, All you need to do is create a subscription on report manager and then use the shared schedule you have created.

Then in your sql agednt job add another step to execute the stored procedure 

[dbo].Runreportschedule 'Myschedule'

This will then send the report immediately

Answer 8

Hi Seyed

This sounds like a nice way of doing it. The problem I am having is how to automate the checking of this control table. Ideally I would like SSRS to send out the report as soon as this table has a new row inserted into it.

To give a bit more detail, I would like to have a control table with a date column and then 4 StoredProcedureRunSuccessfully columns ( I actually want to run the report after the last of four Procs has run). Every day after my Procs have run (or failed) I would like to insert a row into this table, Is it possible to have the insert into this table act as the trigger for SSRS to run the report and email it out?

Thanks very much,

Dave

Hi Dave,

Thanks for your posting.

When we select the "When the report data is updated on the report server" trigger for a Data-Driven subscription, it only works if the following two conditions are satisfied:

1. The report is configured to run from a report execution snapshot.
2. The snapshot is refreshed due to the data update.

So, I don't think inserting the "StoredProcedureRunSuccessfully" columns can trigger the subscription. But you can use the status of the "StoredProcedureRunSuccessfully" column to execute the sp_start_job stored procedure and trigger the subscription. For the detailed information, Syed and Nasa1999 have explained well.

Reference:
Create Data-driven Subscription Page

Regards,
Mike Yin

---

Mike Yin

TechNet Community Support