I tried this out and it is not quite what I was hoping for. What I was looking for a way to log the success/failure of each message (e.g. identified by an id) which went to each recipient so that I could update the message/recipient record if it has successfully sent. e.g. return messageID=3, status=success, firstname.lastname@example.org' so then I can either update that messageid=3 has been successfully sent and/or that email@example.com' has been successfully sent messageid=3.
What 'dbr.mail.log.proc' does is just tell me per emailing (not per recipient nor message) what the results are after they have all gone out. If there is only 1 fixed recipient or just 2-3 and all emails are successfully sent, then this is manageable, but if this went to 200 people (or 2000...), then the to_address value would be very long and need to be parsed to record in each recipient row matched to the email address. Plus there would be no indication of which recipient email failed to send, thus it couldn't be resent as Iäd have no idea who did/did not receive it.
But if one of the emails fails (I tested with a deliberately invalid format email address like '.com' and with 'qwerty@com' as two of three email addresses the query returns), the process sends the valid emails (message: "Sent 0 mail with no errors" (for '.com' seemed to make it hang but still logged "Sent 0 mail with no errors").
I never received a message/error result so I guess these occur only if the smtp server fails?
So I guess I see two options:
- Construct queries where there are multiple rows with multiple recipients in a loop instead with one email each so I can individually capture the recipient email address and error message from dbr.mail.log.proc-command - and make sure all emails match a valid format pattern and hope the pattern gets them all ;-)
- You could allow something to pass custom values to the 'dbr.mail.log.proc' procedure like:
SELECT 'dbr.mail.log.proc', 'sp_mail_log', 'inMessageID=messageID', 'inVersion=versionid'; so that for each row in the recipient-subject-message query it calls sp_mail_log separately (and allow additional columns to be specified after the basic 4 use to send the email).
Options 1 is within my power now but then the only 'new' information the dbr.mail.log.proc-command gives me is the success/error status (every time it will be either "Sent 1 mail with no errors" or "Sent 0 mail with no errors").
A different type of improvement on the results of the dbr.mail.log.proc-command would be to (a) return a general success/failure value (vs "Sent 1 mail with no errors") and if there is an error type (e.g. if "Sent 0 mail with no errors" it might say 'invalid address' or 'smtp authentication error' or something else.
Of course, the constraints you face might be that the email library myDBR uses doesn't allow this type of granular per-recipient feedback and error message and only gives a dump of the error....in which case option 1 is what I'll have to resort to and parse the "Sent 1 mail with no errors" message for if it say 0/1 mail and has the phrase "no errors" or not.
Hope this detailed feedback is helpful - the product is great and always improving, and at least I have a work-around (loop to send each row from the query individually so I can log it more granularly)