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 'email@example.com','firstname.lastname@example.org','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 ("email@example.com" in the example). The name that is specified in the From: argument appears as the sender of the e-mail ("firstname.lastname@example.org" 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: