July 13, 2021 at 2:42 pm
Is there a way to configure DBMail to send the email to a local folder?
July 13, 2021 at 3:22 pm
I'm not sure what you mean. Email is a specific way of sending something. This doesn't produce a file, but sends an email through an SMTP email system.
July 13, 2021 at 4:02 pm
To add to what Steve said, DBMail cannot write to a file on a local folder. If you are looking to write results from a SQL query to file, there are other methods of handling this. Using DBMail is the wrong tool for the job.
Now, that being said, you could do it; it just would require more than just DBMail to handle it. DBMail would be used to send the message to the mail server and then you'd need a second process to grab the mail message from the mail server and store it as a file. I've written similar things in powershell before (wrote a tool that grabbed all unread mail from an Exchange server and dumped them to their own .txt files then marked it as read on Exchange).
So, writing directly from DBMail to disk is impossible without doing a lot of unneeded extra work. It would be much easier to use SQLCMD to execute your query and dump the results to file (my opinion), or powershell to run the query and dump the results to file.
Alternately, if this is for a scheduled report type data dump, SSRS with a subscription is probably going to be a safe bet. Then you can export it in an easily consumable format - Excel!
But I think the big question that we want to know - what is it you are trying to do?
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
July 13, 2021 at 5:23 pm
Actually, the email system in SQL Server is quite robust and a copy of every email is stored in the msdb.dbo.sysmail_allitems table. In fact, it's a bit of a pain in the butt because you do have to create a job to occasionally clean things up if you're sending a lot of emails.
The body of each email, whether in plain text or HTML, is stored in a max datatype in that table (view actually). You can certainly copy the contents of that table to a local table or write it to a local file. So the answer to the original question of...
Is there a way to configure DBMail to send the email to a local folder?
... is "Effectively... kind of yes" but you'll need to build the method. You could use PowerShell or SQLCmd externally or use xp_CmdShell internally to call either to get things to write to files but, yup... can be done!.
Heh... no... I'm not going to do that for you. Lookup more about the msdb.dbo.sysmail_allitems table and some of the other tables that are available in the MSDB database and science it out. 😀 It shouldn't be that difficult to do. 😀
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply