Send emails via SQL, and send emails via SQL

A classic image. Add it to favorites.

 

 

The following information about emails sent using SQL is organized on the official website of Microsoft.

How to configure the iis smtp service as a relay SMTP mail

Configure a smart host

1. Click Start and point toProgram", Point to" Administrative Tools ", and then
   Click Internet Service Manager to open the IIS manager.
2. Open your server tree. Right-click "Default SMTP virtual server" and click "properties ".
3. Find the "pass" tab and click "advanced ".
4. In the "smart host" text box, type the name of your SMTP mail server. If you do not know the name of the SMTP email server, contact your email administrator.
5. Make sure that the Simple Mail Transfer Protocol (SMTP) Service is running. The SMTP service is part of the IIS management service. Therefore, the IIS management service must also be running.

Create a stored procedure for sending a cdonts email

Note:: The companies, organizations, products, domain names, email addresses, logos, names, place names, and events listed in this article are all fictitious. No shadow is intended, and no real company, organization, product, domain name, email address, logo, person name, place name, or event shall be assumed.

You can useCodeCreate a stored procedure in the database to send an email by calling the cdonts object model using the SQL Server Ole automated stored procedure.

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_oasetproperty @ mailid, 'bcc ', @ BCC
Exec @ hR = sp_oasetproperty @ mailid, 'cc', @ CC
Exec @ hR = sp_oasetproperty @ mailid, 'subobject', @ subject
Exec @ hR = sp_oasetproperty @ mailid, 'to', @
Exec @ hR = sp_oamethod @ mailid, 'send', null
Exec @ hR = sp_oadestroy @ mailid
 

 
Next, use the created stored procedure 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 Ole automated stored procedures. If the SQL Server user is not a member of the "SysAdmin" fixed server role, the stored procedure mentioned in this example cannot be used to send emails. In this case, you may have to develop a client application to send emails through cdonts. For example, you can use Microsoft Visual Basic applications. 
 
 
 cdonts sends emails to the local SMTP virtual server. The server then routes the email to the SMTP email server specified in the "smart host" text box. The SMTP mail server sends a message to the e-mail address (someone2@example.com in this example) specified in the to: parameter "). The name specified in the from: parameter 
 is the sender of the email (someone@example.com in this example), and test of cdonts is the subject of the email, the message "It Works" is the body of the email. This email is not CC to others because you do not provide parameters for the "cc" or "BCC" fields.