Viacheslav Eremin | Tune SQL-dbmail COM-component

(SQL) SQL (2022)

Tune SQL-dbmail COM-component

Emailing has main trouble - huge post servers mark smail post servers as SPAM. Therefore first step to emailing without SPAM is sending from huge servers. Huge servers adding to Email a lot of undocumented tags.

So, to avoid SPAM my current project as edge using Office360 and MS Outlook.

Next step is install local SMTP server to IIS, this server present as CDO.Message Com-object and used windows Cdosys.dll. However, using this service directly is painful and many fields is undocumented. Therefore in reality this is only one step to configure Emailing to your site.

Pay attention that Cdosys.dll is only one way to site mailing. In my site you can see a lot of other Email library. Look please to:

So, now in order to use dbMail second step is configure local SMTP server.

As you can see, this local SMTP server connected to Office360. We can also see log of Cdosys.dll.

We can install Metabase Explorer and look to the same data as hierarchy.

So, local SMTP server is tuned and connected to Office360. Next step is setup SQL DbMail.

And now we have DbMail configured profile and connect it to local SMTP relay server. Next step is check all our pyramid of connection.

If Email rich test mailbox, we can go ahead. But firstly we need to understand benefits of using DbMail instead raw Cdosys.dll.

First benefits we see clearly all possible parameters to send Email.

ALTER PROCEDURE [dbo].[sp_send_dbmail]
   @profile_name               sysname    = NULL,        
   @recipients                 VARCHAR(MAX)  = NULL, 
   @copy_recipients            VARCHAR(MAX)  = NULL,
   @blind_copy_recipients      VARCHAR(MAX)  = NULL,
   @subject                    NVARCHAR(255) = NULL,
   @body                       NVARCHAR(MAX) = NULL, 
   @body_format                VARCHAR(20)   = NULL, 
   @importance                 VARCHAR(6)    = 'NORMAL',
   @sensitivity                VARCHAR(12)   = 'NORMAL',
   @file_attachments           NVARCHAR(MAX) = NULL,  
   @query                      NVARCHAR(MAX) = NULL,
   @execute_query_database     sysname       = NULL,  
   @attach_query_result_as_file BIT          = 0,
   @query_attachment_filename  NVARCHAR(260) = NULL,  
   @query_result_header        BIT           = 1,
   @query_result_width         INT           = 256,            
   @query_result_separator     CHAR(1)       = ' ',
   @exclude_query_output       BIT           = 0,
   @append_query_error         BIT           = 0,
   @query_no_truncate          BIT           = 0,
   @query_result_no_padding    BIT           = 0,
   @mailitem_id               INT            = NULL OUTPUT,
   @from_address               VARCHAR(max)  = NULL,
   @reply_to                   VARCHAR(max)  = NULL

Second big benefits, we can see all sended, unsended Email and error list.

SELECT * FROM msdb.dbo.sysmail_sentitems
SELECT * FROM msdb.dbo.sysmail_unsentitems
SELECT *,(SELECT TOP 1 [description] FROM msdb.dbo.sysmail_event_log WHERE mailitem_id = logs.mailitem_id ORDER BY log_date DESC) [description]FROM msdb.dbo.sysmail_faileditems logs

And now we going ahead - preparing to mailing from ASP site.

As first step I have created this COM-object https://github.com/Alex-1557/SQL-dbmail-COM-component. And register it in server.

And second huge problem is correct setup account to support attachment. This problem named impersonation. In reality we need to create account for IIS inside SQL server:

CREATE LOGIN [NT AUTHORITY\IUSR] FROM WINDOWS

And than we need to set needed permission to this account.

That's it, now we can create complex test.

   1:  <%@ Language=VBScript%>

   2:  <!DOCTYPE html>

   3:  <html>

   4:  <body>

   5:  <form name='TDS' method='post' ID="test1" action="/TST4.asp">

   6:  <a href="#">Test mail component with attachment.</a> <br><br>

   7:  Set title.<br>

   8:  <input type="text" name="title" style="width: 500px;"><br>

   9:  Set body.<br>

  10:  <input type="text" name="body" style="width: 500px;"><br>

  11:  Set EmailTo:<br>

  12:  <input type="text" name="Mailto" style="width: 500px;"><br>

  13:  With attachment<br>

  14:  <input type="checkbox" name="att"><br>

  15:  <br>

  16:  <br>

  17:  <input type="submit" value="Submit">

  18:  </form>

  19:  <%

  20:  'tds.asp

  21:  Dim ATT_tds_STRING

  22:  ATT_tds_STRING = "Data Source=localhost; Integrated Security=SSPI"

23:

  24:  Dim chAtt, Mailer1, Res1

  25:  chAtt = Request.Form("att") & ""

  26:  Set Mailer1 = Server.CreateObject("TDS.DbMail")

27:

  28:  if IsPostBack then

  29:      if chAtt="" then

  30:          Res1 = Mailer1.DbMailSendMail (ATT_tds_STRING, "365", "", Request.Form("Mailto"), "", "", Request.Form("title"), Request.Form("body"), "" )

  31:      else

  32:          Res1 = Mailer1.DbMailSendMail (ATT_tds_STRING, "365", "", Request.Form("Mailto"), "", "", Request.Form("title"), Request.Form("body"), "C:\www\tdsv5\Images\BeOS_Help.gif")

  33:      end if

  34:      Response.Write (Res1)

  35:  End IF

36:

37:

  38:  Function IsPostBack()

  39:  If Request.ServerVariables("Request_Method") = "POST" Then

  40:     IsPostBack="True"

  41:  Else

  42:     IsPostBack="False"

  43:  End If

  44:  End Function

  45:  %>

  46:  </body>

  47:  </html>

And try to check all our pyramid.

Comments (

)

<00> <01> <02> <03> <04> <05> <06> <07> <08> <09> <10> <11> <12> <13> <14> <15> <16> <17> <18> <19> <20> <21> <22> <23>

Link to this page: http://www.vb-net.com/SQL-dbmail/Index.htm