sp_send_dbmail Options

Recently one of my projects had a requirement to send email from SQL Server to certain recipients. A developer asked if they could use sp_send_dbmail  and I said yes, I use it all the time and didn’t think too much about it until I got an email from her mentioning an issue they were having. Researching the problem more I found a solution I was not aware of and wanted to share it.

Problem

The problem they were having was with the way the “From” address was resolving. They wanted it to read “Do Not Reply” but what was happening was the email header resolved to the Server name it was sent from.

Researching the sp_send_dbmail  did not provide much insight so I started experimenting a little. One option I provided to them was the use of Powershell. I use that on one of my servers and the “From” email resolves to what I name it in the script. I provided the simple 7 lines of code as an example and suggested I could help them work it since she was not familiar or comfortable with using Powershell.

This is the Powershell code I use for sending email.

powershell script

I suggested she send me the stored procedure she was planning on using so I could see what she was doing and potentially convert to Powershell. If not the entire stored procedure I envisioned at the very least the sending of the email function.

Solution

While I was waiting on her reply I continued to experiment with the sp_send_dbmail  and discovered this little trick.

DO NOT REPLY

Conclusion

Almost every problem, issue or requirement will have multiple ways to solve it. What the best solution for you may not work for someone else. It may not work each time you need to use it where you work. Discovering different uses or ways of doing the same thing is one of the reasons I love what I do. Sharing what I have learned is one way I try to pay it forward.

Cheers!

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s