I have a project that requires me to send contracts that are stored in SQL Server to customers and I started poking around for the best way to do it. First, I found a quick code snippet that sends an email using sp_send_dbmail:
EXEC msdb.dbo.sp_send_dbmail @recipients = 'lsmithmier@productiveedge.com', @body = 'Just testing the mail', @subject = 'Sending Mail using Database Mail' ;
When I first ran it on my database server (SQL Server 2008 R2) I got the following error:
Msg 15281, Level 16, State 1, Procedure sp_send_dbmail, Line 0 SQL Server blocked access to procedure 'dbo.sp_send_dbmail' of component 'Database Mail XPs' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'Database Mail XPs' by using sp_configure. For more information about enabling 'Database Mail XPs', see "Surface Area Configuration" in SQL Server Books Online.
So I cranked up Microsoft SQL Server Management Studio and set email up:
1. Open up the Management section and right click on the Database Mail item:
2. Choose Configure Database Mail
3. Click Next
4. Click Next
5. Click Yes
6. Set the Profile name and choose Add an SMTP account:
7. Enter your settings:
8. Set the profile as both Public and Default:
9. Click Next
10. Click Finish
11. Click Close
and it is all setup! Really easy compared to the old way of setting email up with SQL Server. Then I re-ran the query and got the following response:
Mail queued.
But then what? I ran a quick query and found that the email wasn’t sent correctly:
use [msdb] SELECT * FROM sysmail_mailitems GO SELECT * FROM sysmail_log GO
so I looked back over my settings and updated the port number to hit the outgoing relay and everything started working!
