Automatic Report Generation (crystal reports)
-
Greetings!
I want to create reports upone certain triggers and email them to users. To keep things flexible, I was going to use Crystal Reports to generate the result to be mailed, and then use xp_sendmail to send the message on its way.
Is there a Crystal Reports procedure?
If not, would the best way be:
- Create/use a command line interface and use xp_cmdshell to execute it
- Create an xp_ExecCrystalReport(rptimage, outfmt, xtraparms) as outimage
- Use the sp_OA OLE automation Objects
Any thoughts?
TIA,
Thor Johnson
-
This was removed by the editor as SPAM
-
My company had the same sort of problem, but the way we resolved it (can't claim it's the most efficient, but it works) is by writing a VB app using the Crystal OCX to generate the reports for us. The app takes parameters, so it will take a blank report and generate the report (along with the contained data) on the fly. From there, the trigger could call this external program and generate new copies of the reports you want to send. At this point, we let the users know that a new copy of the report is available on our network server, but we don't actually send them the report. I don't know how you would go about attaching the report (or if that's really even the way to go), but maybe this will spawn somebody else to complete the answer. Hope this helps!
Ryan
-
I read a column about this type of thing where it was called a "Worst Practice". THe issue with calling an outside program from a trigger is that if that call fails your ENTIRE transaction will be rolled back, so your data modification fails. The recommended solution is to store the changes in a queed table and monitor that on a regular basis and then create and send your report.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question -
Couldn't you use the "AFTER" modifier on the trigger to specify that it runs after the update/insert/delete has happened? I'm not too familiar with triggers, but it seems like this would work.
Ryan
-
I read a column about this type of thing where it was called a "Worst Practice". THe issue with calling an outside program from a trigger is that if that call fails your ENTIRE transaction will be rolled back, so your data modification fails. The recommended solution is to store the changes in a queed table and monitor that on a regular basis and then create and send your report.
Couldn't you use the "AFTER" modifier on the trigger to specify that it runs after the update/insert/delete has happened? I'm not too familiar with triggers, but it seems like this would work.
Thank you for the suggestions! It actually turns out that our client insists on plain text, so I ended up rolling my own "report-like" generator.
But I did want to comment on the above: It was mention here (sqlservercentral) that a failure could roll back the update, and I thought that wouldn't happen if it was in an after trigger. It turns out that a failure in an after query does roll the change back (urgh?! not what I expected/wanted). Worse: if you try to insulate it with an sp_executesql command, certain falures still roll back the update (raiserror messages won't roll it back, but something like sp_executesql N'selorct * from foo' that causes a syntax error will roll it back).
Ick. So, in my trigger, I just end up doing 'select * into QueueTable from inserted' in the trigger, and kick of a job every 5 minutes to look at QueueTable and do whatever I was supposed to do with it. Bleh.
-Thor
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply