Send Email directly without SMTP server(MX DNS lookup) in SQL Server Stored Procedure¶

Introduction¶

In general, we send email via specified SMTP server. How does the specified SMTP server know what address this email should be sent to? The answer is… it queries MX record of recipient’s domain via DNS lookup. It then forwards this email to the SMTP server queried from DNS server. If recipient’s server doesn’t work fine, sender’s SMTP server will send a failure-delivery report to the sender telling it failed to send out the email.

How does EASendMail SMTP component work with “Send email directly”? Firstly, it queries MX record for recipient address from DNS, then sends email to recipient’s email server directly. In short, if no SMTP server is specified in the code, EASendMail will send email to recipient directly. Since querying DNS server consumes CPU time and networking resource, the performance of “Send email directly” is lower than sending email with specified SMTP server. Moreover, nowadays more and more SMTP servers block email sent from dynamic IP address, so we don’t recommend you to use “Direct Send Email” except you have a static IP address or you encounter problem with your ISP SMTP server.

Every recipient may have different SMTP server, if there are multiple recipients in one message and you want to send email directly, you should send the email to the recipients one by one.

To implement this feature, you just need to put nothing to SMTP server address.

[SQL Stored Procedure Example - Send email without specified SMTP server (MX record DNS lookup)]¶

The following example codes demonstrate how to use EASendMail SMTP component to send email using DNS lookup.

/* Set SMTP server address to '' */
DECLARE @ServerAddr nvarchar(128)
Set @ServerAddr = ''

DECLARE @From nvarchar(128)
Set @From = 'test@emailarchitect.net'

DECLARE @To nvarchar(1024)
/*You can input multiple recipients and use comma (,) to separate multiple addresses */
Set @To = 'support@emailarchitect.net'

DECLARE @Subject nvarchar(256)
Set @Subject = 'simple email from SQL server'

DECLARE @Bodytext nvarchar(512)
Set @BodyText = 'This is a test text email from MS SQL server directly, do not reply.'

/* Do not set user authentication */
DECLARE @User nvarchar(128)
Set @User = ''
DECLARE @Password nvarchar(128)
Set @Password = ''

/* Do not set  SSL connection*/
DECLARE @SSL int
Set @SSL = 0

/* Set 25 default SMTP port */
DECLARE @Port int
Set @Port = 25

exec usp_SendTextEmail @ServerAddr, @From, @To, @Subject, @BodyText, @User, @Password, @SSL, @Port

With above code, if you get error like “5xx IP address rejected”, that means your IP address is blocked by the recipient’s SMTP server. You have to specify a SMTP server with user authentication to relay your email.

Remarks

In my solid experience, I don’t suggest that you send email directly.

• If your IP address is dynamic, most SMTP servers reject your connection due to anti-spam policy. We always suggest that your send email by a SMTP server that has a static internet IP address. When you relay email by your SMTP server, because you do user authentication at first before you send email to your SMTP server, so your SMTP server doesn’t reject your connection even your IP address is dynamic. Finally your SMTP server sends email to remote SMTP server. Because your SMTP server has a static IP, the email won’t be rejected by remote SMTP server.

  

• If you encountered a temporal SMTP error (4xx), you should retry to send email later. That means you have to write the code to handle retry. So if you have a static IP address, I suggest that you use EASendMail Component + EASendMail Service, EASendMail service can send email directly or send email with specified SMTP server in background and handle delivery retry automatically.

To learn more detail about EASendMail Serivce, please have a look at Work with EASendMail Service (Email Queuing).

Next Section

At next section I will introduce how to send multiple emails with cursor in SQL stored procedure.