2

Is there a way to send email from SQL server stored procedures without using DBMail? DBMail is hard coded by default to use base64 encoding for email messages. As far as I can tell there's no way to override this.

We need to parse/process messages on our mail server and base64 encoded messages can't easily be parsed. Rather than trying decode them we'd like to send email using some other method.

Preferably one that doesn't use CDOSYS or CDONTS either? Why? Because we're migrating to Windows Server Core and Server Core doesn't support CDOSYS or CDONTS.

See here: https://technet.microsoft.com/en-us/library/hh922965.aspx

Specifically This: 

However, before you attempt to host a Web site on Server Core, it’s worth noting 
code that makes use of System.Web.Mail will fail because of the dependency on 
CDOSYS (which does not exist in Server Core). 

Microsoft recommends modifying Web application code to use System.Mail.Net 
instead of System.Web.Mail.

This is refering asp/.net code on a web server but presumably the same restriction applies to SQL server installed on a Core server.

So with those two limitations in mind (no dbmail, and no cdonts/cdosys) how can we send mail from stored procedures? Any ideas? :)

Thanks! Brad

Brad
  • 191
  • 1
  • 7

1 Answers1

0

Configure the Smart Host:

  • Click Start, point to Programs, point to Administrative Tools, and then click Internet Services Manager to open the IIS Manager.
  • Open the tree for your server. Right-click Default SMTP Virtual Server, and then click Properties.
  • Locate the Delivery tab, and then click Advanced.
  • Type the name of your SMTP mail server in the Smart Host text box. If you do not know the name of your SMTP mail server contact your mail administrator.
  • Make sure that the Simple Mail Transfer Protocol (SMTP) service is running. The SMTP service is part of the IIS Admin Service. Therefore, the IIS Admin Service must also be running.

Create a Stored Procedure to Send CDONTS E-Mail:

You can use code that is similar to the following to create a stored procedure in your database that sends e-mail by using the SQL Server OLE Automation stored procedures to invoke the CDONTS object model.

CREATE PROCEDURE [dbo].[sp_send_cdontsmail]  @From varchar(100),  @To varchar(100),  @Subject varchar(100),  @Body varchar(4000),  @CC varchar(100) = null,  @BCC varchar(100) = null  AS  Declare @MailID int Declare @hr int  EXEC @hr = sp_OACreate 'CDONTS.NewMail', @MailID OUT EXEC @hr = sp_OASetProperty @MailID, 'From',@From  EXEC @hr = sp_OASetProperty @MailID, 'Body', @Body  EXEC @hr = sp_OASetPropert, @MailID, 'BCC',@BCC  EXEC @hr = sp_OASetProperty @MailID, 'CC', @CC EXEC @hr = sp_OASetProperty @MailID, 'Subject', @Subject EXEC @hr = sp_OASetProperty @MailID, 'To', @To  EXEC @hr = sp_OAMethod @MailID,'Send', NULL  EXEC @hr = sp_OADestroy @MailID

Next, use the stored procedure that you created and provide the correct parameters: exec sp_send_cdontsmail 'someone@example.com','someone2@example.com','Test of CDONTS','It works' Note Only members of the sysadmin fixed server role can run the OLE Automation stored procedures. If the SQL Server user is not a member of the sysadmin fixed server role, you cannot use the stored procedure that is mentioned in the example to send e-mail. In such a situation, you may have to develop a client application to send e-mail with CDONTS. For example, you can use a Microsoft Visual Basic application.

CDONTS [CDO for Microsoft Windows NT Server - It's a Simple Mail Transfer Protocol (SMTP) specific OLE server that is specifically designed to provide messaging functionality to Web-based applications], CDONTS sends the e-mail to the local SMTP virtual server. The server then routes the e-mail to the SMTP mail server that is specified in the Smart Host text box. The SMTP mail server sends the mail to the e-mail address that is specified in the To: argument ("someone2@example.com" in the example). The name that is specified in the From: argument appears as the sender of the e-mail ("someone@example.com" in this example) with the subject "Test of CDONTS" and the message "It works" as the body of the e-mail. No one is copied on the e-mail because you did not supply an argument for the CC or for the BCC field. You can modify the example so that it will send HTML-based e-mail or attachments.

For documentation about CDONTS, visit the following Microsoft Web site:

http://msdn.microsoft.com/library

https://support.microsoft.com/en-us/help/312839/how-to-send-e-mail-without-using-sql-mail-in-sql-server

Community
  • 1
CR241
  • 1,565
  • 3
  • 18
  • 32