2

This question already has an answer here:

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

marked as duplicate by joanolo, Colin 't Hart, LowlyDBA, Philᵀᴹ, Joe Obbish Jun 18 '17 at 2:34

This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.

  • 2
    Would a powershell job from SQL Server be an option for you? Powershell makes it pretty easy to send emails and work with base64. – Ali Razeghi Jun 7 '17 at 23:42
  • I somewhat considered that although at times I need to send a LOT (read hundreds of thousands) of emails I'm not sure how resource intensive calling a powershell script would be? – Brad Jun 7 '17 at 23:50
  • What I've encountered more in large batches like that is overrunning my memory buffer by not optimizing the looping mechanism used to reiterate through the list. This almost sounds like you should consider running this from a different server and get the data from MS SQL Server first. This way if the powershell/app process bombs or something, it's not eating up threads running as a phantom app in the background which might interfere with your next run, or worse, your prod SQL Server. Have you considered a diff mail server for sending the mail? Just reading the logs for dbmail would suck. – Ali Razeghi Jun 8 '17 at 0:10
  • @nelsoncasanova - The "answer" used there seems to still rely on dbmail as far as I can tell. That said the solution below that using SQL CLR looks like it would do the trick. I think that's probably the best solution. Unless someone else has a better/more efficient answer? – Brad Jun 8 '17 at 0:35
  • 1
    Just a thought - Would it be possible to 'load' the email data into a Sql Server table (with maybe a timestamp as to the load time) and then have a Sql Server job run a Powershell process periodically to process that table as a queue only peeling off data up to the current time. I'm not sure of the actual logistics involved - just blue skying. – Scott Hodgin Jun 8 '17 at 9:41
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

Not the answer you're looking for? Browse other questions tagged or ask your own question.