Use table trigger to call SSRS report

Question

Use table trigger to call SSRS report

stephen.woolston

SSC Veteran

Points: 236
More actions
February 25, 2016 at 5:27 am

#305030

I have a situation in a hospital such that when a patient is registered for which the application creates an alert (within the application), they want a SSRS report to be run and emailed to a recipient as well. I thought creating/using a trigger on the registration table might work, but I can't find any information as to how to execute a SSRS report within a trigger.
Does anyone know how to accomplish this, or is there some other way to do it?
Any help is greatly appreciated!

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

tindog SSCarpal Tunnel Points: 4892 More actions · Answer 1

I've used SSIS for something similar before and can walk you through what I did if that's an option for you?

Essentially I had an SSIS package run by a SQL agent job. It would read a table for the report to run and parameters to use, put together a URL with excel render and then save the report in the specified location.

(So I suppose in your case, you'd want to trigger the job, and also pass an e-mail address, and you could then send the file to the intended recipient)

I don't think I've come across any other way unless a subscription is already set up - but sounds like that's not possible in your case.

stephen.woolston SSC Veteran Points: 236 More actions · Answer 2

Thanks for your response.

Wouldn't an Agent job basically work the same as a data-driven SSRS subscription? At a scheduled time, the database is queried and if the conditions are met, a report is generated. I can do that, but it seems like a lot of unnecessary processing.

It seemed a trigger that executes the report would be a more streamlined way to do it.

Has anybody ever done that?

tindog SSCarpal Tunnel Points: 4892 More actions · Answer 3

If you're able to do data-driven subscriptions, why not just do that? I wouldn't have thought it would be much work to set up.

stephen.woolston SSC Veteran Points: 236 More actions · Answer 4

I can, but it seems a waste of resources to have this subscription running every minute or every few minutes with only sporadic data to capture. I have another data driven subscription running every 15 minutes that occasionally misses data it should report, and I thought a trigger might be more reliable.

tindog SSCarpal Tunnel Points: 4892 More actions · Answer 5

Ah I see. You could find the job ID of the data driven subscription by using the reportserver database, and then simply execute this job in your trigger.

Do you know why it misses data?

stephen.woolston SSC Veteran Points: 236 More actions · Answer 6

Thanks for your help tindog.

I haven't been able to figure out why the report sometimes misses data.

If I could ask one more thing, what would the syntax of the execute command you suggested look like?

Thanks

Steve

tindog SSCarpal Tunnel Points: 4892 More actions · Answer 7

No problem.

It might be worth trying to find out why it happens in case it's likely to continue with a trigger.

You can use:

EXEC msdb.dbo.sp_start_job @jobid = 'job id of dds'

(https://msdn.microsoft.com/en-gb/library/ms186757(v=sql.100).aspx)