SQL Trigger to email PDFs

[DJH]

Hi

We have a requirement to email certain report pdfs to users once they have run them. To do this I have setup an SQL trigger on the SVM810.F986110 table, every time a row is updated and the status column is set to 'D' it gets the email address of the user and sends the pdf (which is also in the table). The email bit works fine but for some reason the runbatch.exe doesent terminate on the UBE Server. This obviously means that all other jobs stay at waiting status until I manually kill the runbatch.exe process. Obviously this is not ideal!

I have two questions:

1. Does anyone have any ideas why the runbatch.exe process is not terminating?

2. Is a trigger on the SVM810.F986110 the correct way to do what I want or will it cause problems?
I have seen an alternative way on JDEtips but it looks too complicated and this way seems far easier to implement.

Thanks in advance

We are on ERP8.10 running SQL2000, all Intel(Windows 2000 server).

 

[Larry_Jones]

Here's a stab in the dark (wild guess):

What location (directory) is the PDF file in when the Status changes to "D"? If it is not the PrintQueue directory then a File move still needs to be done to move it there and your email operation puts an unexpected lock on the file ...

 

[JEMILLER]

As Larry pointed out you may be dealing with a locking issue. How are you sending the email? Are you using the CDONTS functions within your trigger. I suggest that you perform the actual email processing outside of the trigger. You might consider using sp_start_job to submit a procedure or external command file asynchronously to send the email. This would allow the trigger to finish immediately and hopefully allow the runbatch process to complete before your email process needs it.

I have another possible solution for you:

** Commercial plug alert **

We are conducting a final beta release of UnityOSA. This is an Output Stream Access library that integrates with the runbatch process and will allow you to (among other things) send the PDF/CSV file upon job completion. Please contact me if you are interested in trying it out: [email protected]

Justin Miller

 

[altquark]

Just in case you didn't know - there is always Alex Pastuhov's "Autoprint" which does exactly this function - PLUS it completely helps manage printers against versions etc, for only a few dollars. I certainly wouldn't put triggers on the F986110 personally.

 

[FNorelli]

I use a product called Report2Web from Redwood Software. With the Burst utility, you can take a large PDF, split it into many smaller reports using the level break field, and email the parts to different users. For example, we have building operation reports that go out every month. The report is run on the JDE scheduler on the last day of the month. A custom UBE copies the PDF from the application server to the Redwood server, which automatically splits up the report by cost center. In R2W, there is an email subscription list that says which group of people get which slice of the report. It's a pretty slick tool. We use it to automatically parse and email 6 monthly building operation reports and about 16 weekly integrity reports. Email me if you want to know more.