'Schedule' a report to run from a trigger

[jymm]

I have a need to print an SSRS report when a trigger is fired on a table. I MUST be missing something here... can someone point me in the right direction please?

 

[RiverGuy]

SQL Server Reporting Services is reporting software which comes bundled with SQL Server. It is not part of the database engine. Triggers are part of the database engine. There is no native way to run a report from a trigger. You may be able to code some operating system command or call to a batch file, but those type of operations don't really belong in triggers. Why not set up some sort of service to poll the database at intervals and run a report if a certain condition is true?

 

[jymm]

yup - know all about ssrs and sql 2005/2008... everything I have is in there since ssrs & sql 2000.

my problem is that I need this to be user specific. By that I mean that if domain User Jymm has access to a 'function' and he inserts a row of data I need the control document to print out for FDA (and other regulatory folks) with some info that I have already entered and some that needs to be filled in by hand or distributed according to the regulatory agency. SO I do not want to print it off on a specific printer - I need it to go to their default printer (or let the user control where it is printing). Polling is not really an option - needs to happen right away.

so - for example when a user is in one of my reports - I can throw them to an ASP page which is an external vendor ASP page that will allow them to update their data... kinda the same thing here except I need to do it in reverse.

Let's put it this way - BACK IN THE DAY - I was able to do a trigger that would fire a batch file which would print a Crystal (ick pooey) report that would print accordingly. I could load the default printer as a parameter to the bat file/function --- need to do (essentially) same here.

 

[RiverGuy]

Polling is not really an option - needs to happen right away

What happens if the printer is offline or out of paper? It might take a few minutes to reset the printer. I'm just saying that I've not heard of a situation where a print out needs to instantly hit a printer as soon as a record is inserted. The more you get away from SQL Server functionality in triggers, the more room for debugging nightmares you introduce.

But, think about it from an overall systems architecture viewpoint. If there are a bunch of regulations which need to be followed, I would assume that the users are allowed to make data modifications through only pre-defined applications and stored procedures. Why not put the code in the application itself? You can then add some audit tables to ensure you have a record in case data gets into a table through other means.

 

[jymm]

sorry none of your options help me much - I know there is a way - thought the tek-tips folks would remember more than me. I will continue the search

 

[jymm]

THIS is close - but I remembered something else

http://blogs.msdn.com/lukaszp/archive/2005/10/07/478391.aspx

 

[behonest]

Hi jymm,

have you got the solution. Because i too searching for the same task like follows.

in DB if any record inserted/updated then i required a printout from Database.

Thanks in advance.

 

[jymm]

short answer - nope - did not do ever find the solution to this one - temporary solution ('till Jymm gets time) is for me to do it through a Rockwell piece of software - does not give me the felxibility that I ultimatly want... so if you figure it out...

 

[Banjo99]

It cannot be done within SSRS, but you can achieve event-based scheduling for SSRS reports using SQL-RD by ChristianSteven Software.

http://www.christiansteven.com