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:
- Use Twilio or other 3-rd party service..
- MailKit/MimeKit - best eMail client.
- ASP.NET Core Backend with MailKit/IMAP (Async/Await, Monitor, ConcurrentQueue, CancellationTokenSource)
- Избавляемся от MS Exchange на Window-платформе (hMailServer + ASSP).
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.
<SITEMAP> <MVC> <ASP> <NET> <DATA> <KIOSK> <FLEX> <SQL> <NOTES> <LINUX> <MONO> <FREEWARE> <DOCS> <ENG> <CHAT ME> <ABOUT ME> < THANKS ME> |