Creating / generating a '.eml' file from SQL Server dbmail

Question

Is it possible to generate .eml file from SQL Server dbmail? I running SQL Server 2008 R2.

I have a requirement to create an ‘email sandbox’ where email will be staged before being delivered to our SMTP server for delivery. This will allow application generated (and hopefully dbmail), email to be viewed and approved before final delivery. I can achieve this in .NET by specifying a ‘pickupDirectoryLocation’ property for a SmtpClient object.

Questions: is there a way I can do this with SQL Server generated email as well?

Thanks

Answer 1

nice question! Never thought of that.

well, when you call the send_dbmail proc, you are not actually sending the email. You are just inserting a record on a queue that will be read by the Database Mail process (DatabaseMail.exe) that will send the e-mail.

Don't know if what you want can be achieved, but I can think of some things:

1. You can use sysmail_configure_sp with parameter AccountRetryDelay or DatabaseMailExeMinimumLifeTime (not sure, never tried myself) to delay the time the process runs so managers can have some time to approve the email (or enable\disable the job at certain times). In this mean time you can access the table that holds the messages and edit them. But that wouldn't fully solve your problem and its rather a ugly solution. More info here

2. Create an intermediate table where you insert your messages there and after the approval, you call the send_dbmail procedure