Microsoft KB Archive/312839

= How to send e-mail without using SQL Mail in SQL Server =

Article ID: 312839

Article Last Modified on 9/13/2006

-

APPLIES TO


 * Microsoft SQL Server 2000 Standard Edition
 * Microsoft SQL Server 7.0 Standard Edition

-



This article was previously published under Q312839



IN THIS TASK
SUMMARY How to Use CDO for Microsoft Windows NT Server (CDONTS)
 * Configure the Smart Host
 * Create a Stored Procedure to Send CDONTS E-Mail

How to Use CDO for Microsoft Windows 2000 (CDOSYS)
 * Create a Stored Procedure to Send CDOSYS Mail

REFERENCES



SUMMARY
SQL Mail provides an easy way to send and read e-mail from Microsoft SQL Server. However, because SQL Mail is a MAPI application, a MAPI subsystem must be present on the server. Microsoft Windows NT 4.0 installs a MAPI subsystem when you install Windows Messaging. However, Microsoft Windows 2000 does not provide a MAPI subsystem. Therefore, if you want to use SQL Mail, you must install a MAPI client like Microsoft Outlook.

You can use other methods to send Simple Mail Transfer Protocol (SMTP) e-mail directly from SQL Server. For example, you can use Collaboration Data Objects for NT Server (CDONTS) or Collaboration Data Objects for Windows 2000 (CDOSYS) in combination with the sp_OA SQL Server OLE Automation stored procedures. This article contains examples of how to use these techniques to send e-mail to an Internet mail server. You can modify the techniques so that they will provide a more robust mailing system. For example, you may want to add error handling code. The methods in the examples do not provide a means for reading or for processing e-mail.

Note CDOSYS is available starting with Windows 2000, and we recommend that you use CDOSYS instead of CDONTS. CDONTS is not supported with Windows Server 2003 and later operating systems.

Note These programming examples illustrate techniques that you can use to invoke the CDO object model in Transact-SQL. You are responsible for any programming extensions to the examples or for any stress testing that is required for running the code in a production environment.

Microsoft provides programming examples for illustration only, without warranty either expressed or implied. This includes, but is not limited to, the implied warranties of merchantability or fitness for a particular purpose. This article assumes that you are familiar with the programming language that is being demonstrated and with the tools that are used to create and to debug procedures. Microsoft support engineers can help explain the functionality of a particular procedure, but they will not modify these examples to provide added functionality or construct procedures to meet your specific requirements.

How to Use CDO for Microsoft Windows NT Server (CDONTS)
CDONTS is a Simple Mail Transfer Protocol (SMTP) specific OLE server that is specifically designed to provide messaging functionality to Web-based applications. As such, CDONTS supports sending HTML-based e-mail. Mapi-based applications, like SQL Mail, do not. By default, Microsoft Internet Information Server (IIS) 4.0 or later, installs CDONTS. By default, Microsoft Windows 2000 installs Microsoft Internet Information Server (IIS) 5.0.

For more information about CDONTS, click the following article number to view the article in the Microsoft Knowledge Base:

177850 What is the difference between CDO 1.2 and CDONTS?

CDONTS sends e-mail through SMTP to the local server. If you do not have an SMTP server on your local server, you can use the SMTP virtual server that IIS installs to route your SMTP e-mail to the appropriate SMTP mail server. To use CDONTS to send e-mail from SQL Server, follow these steps:
 * 1) Install IIS and have it running on the computer that is running SQL Server.
 * 2) Specify your SMTP mail server as your &quot;smart host&quot; so that the IIS SMTP service automatically routes any SMTP e-mail that is sent to your local server to your SMTP mail server for delivery.
 * 3) Create a stored procedure in SQL Server that you can use to send e-mail.

When you use CDONTS instead of SQL Mail, you do not have to have a mail client like Microsoft Outlook installed on the computer that is running SQL Server. You also do not have to have a Microsoft Exchange server. You can use any mail server that supports SMTP e-mail as your &quot;smart host.&quot; However, you cannot read and process e-mail that is sent to the computer that is running SQL Server by using CDONTS. You also cannot replace the functionality of SQL Agent Mail.

The instructions that follow rely upon Microsoft Windows 2000. If IIS 4.0 is installed on the computer, you can configure Microsoft Windows NT 4.0 similarly. However, the steps will differ slightly.

For more information about how to configure the &quot;smart host&quot; on Microsoft Windows NT 4.0, click the following article number to view the article in the Microsoft Knowledge Base:

230235 How to configure the IIS SMTP service to relay SMTP mail

Configure the Smart Host

 * 1) Click Start, point to Programs, point to Administrative Tools, and then

click Internet Services Manager to open the IIS Manager.
 * 1) Open the tree for your server. Right-click Default SMTP Virtual Server, and then click Properties.
 * 2) Locate the Delivery tab, and then click Advanced.
 * 3) 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.
 * 4) 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
Note The example companies, organizations, products, domain names, e-mail addresses, logos, people, places, and events depicted in this article are fictitious. No association with any real company, organization, product, domain name, e-mail address, logo, person, places, or events is intended or should be inferred.

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_OASetProperty @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 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 (&quot;someone2@example.com&quot; in the example). The name that is specified in the From: argument

appears as the sender of the e-mail (&quot;someone@example.com&quot; in this example) with the subject &quot;Test of CDONTS&quot; and the message &quot;It works&quot; 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

In the left pane, expand Messaging and Collaboration, expand Collaboration Data Objects, and then expand CDO 1.2.1.

You can find documentation for the SQL Server OLE Automation stored procedures in SQL Server 2000 Books Online.

back to the top

How to Use CDO for Microsoft Windows 2000 (CDOSYS)
CDOSYS provides an object model for developing messaging applications on Microsoft Windows 2000. It also provides enhanced functionality over the existing CDO for Windows NT Server (CDONTS) library. CDOSYS requires Windows 2000, and a local or a remote SMTP server.

You can configure CDOSYS to point to SMTP servers programmatically to give developers flexibility in configuring SMTP servers.

For more information about CDOSYS, click the following article number to view the article in the Microsoft Knowledge Base:

195683 Relationship between 1.x CDO libraries and CDOSYS.DLL

Because CDOSYS can send e-mail to a remote SMTP server, CDOSYS does not require that you have Internet Information Server installed and running on the computer that is running SQL Server. You also do not have to configure the SMTP virtual server. All you have to do is to create a stored procedure in SQL Server that you can use to send e-mail.

When you use CDOSYS instead of SQL Mail, you do not have to have a mail client like Microsoft Outlook installed on the computer that is running SQL Server. You also do not have to have an Exchange server. You can use any mail server that supports SMTP mail as your remote SMTP mail server. However, you cannot read or process e-mail that is sent to SQL Server if you use CDOSYS. You also cannot replace the functionality of SQL Agent Mail.

Use of CDOSYS with the SQL Server OLE Automation object relies on SQL Server OLE Automation to invoke the CDOSYS object model. This was tested with SQL Server 2000 Service Pack 1 (SP1) and SQL Server 2000 Service Pack 2 (SP2). Microsoft does not guarantee that CDOSYS can be called from the OLE Automation stored procedures on versions of SQL Server that were released before SQL Server 2000 SP1.

Create a Stored Procedure to Send CDOSYS 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 CDOSYS object model.

In this sample, we provide an optional degree of error handling to check the hresult of each EXEC sp_OA* statement. If the hresult (@hr) is zero, the previous sp_OA* statement was a success. However, if that hresult is nonzero, the previous sp_OA* statement failed.

For each nonzero hresult failure, a row is inserted into the table [dbo].[cdosysmail_failures] that notes the place and time of the failure. Additionally, the last error is interpreted by using the sp_OAGetErrorInfo stored procedure to report the source and description of the error. That error description is printed to the client connection. Also that error description, time, and place of failure are inserted into the table [dbo].[cdosysmail_failures] in the database so that you can identify and diagnose failures after the fact.

If the sp_OAGetErrorInfo stored procedure itself fails, the example prints the following error message to the client connection:

sp_OAGetErrorInfo failed

-- drop old cdosysmail_failures table if exists IF (EXISTS (SELECT * FROM dbo.sysobjects WHERE name = N'cdosysmail_failures' AND type='U')) DROP TABLE [dbo].[cdosysmail_failures] GO -- Create new cdosysmail_failures table CREATE TABLE [dbo].[cdosysmail_failures] ([Date of Failure] datetime,        [Spid] int NULL,        [From] varchar(100) NULL,        [To] varchar(100) NULL,        [Subject] varchar(100) NULL,        [Body] varchar(4000) NULL,        [iMsg] int NULL,        [Hr] int NULL,        [Source of Failure] varchar(255) NULL,        [Description of Failure] varchar(500) NULL,        [Output from Failure] varchar(1000) NULL,        [Comment about Failure] varchar(50) NULL) GO

IF (EXISTS (SELECT * FROM dbo.sysobjects WHERE name = N'sp_send_cdosysmail' AND type='P')) DROP PROCEDURE [dbo].[sp_send_cdosysmail] GO

CREATE PROCEDURE [dbo].[sp_send_cdosysmail] @From varchar(100) , @To varchar(100) , @Subject varchar(100)=&quot; &quot;, @Body varchar(4000) =&quot; &quot; /*********************************************************************   This stored procedure takes the parameters and sends an e-mail. All the mail configurations are hard-coded in the stored procedure. Comments are added to the stored procedure where necessary. References to the CDOSYS objects are at the following MSDN Web site: http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cdosys/html/_cdosys_messaging.asp ***********************************************************************/       AS       Declare @iMsg int Declare @hr int Declare @source varchar(255) Declare @description varchar(500) Declare @output varchar(1000) --************* Create the CDO.Message Object ************************ EXEC @hr = sp_OACreate 'CDO.Message', @iMsg OUT IF @hr <>0 BEGIN SELECT @hr INSERT INTO [dbo].[cdosysmail_failures] VALUES (getdate, @@spid, @From, @To, @Subject, @Body, @iMsg, @hr, @source, @description, @output, 'Failed at sp_OACreate') EXEC @hr = sp_OAGetErrorInfo NULL, @source OUT, @description OUT IF @hr = 0 BEGIN SELECT @output = ' Source: ' + @source PRINT @output SELECT @output = ' Description: ' + @description PRINT @output INSERT INTO [dbo].[cdosysmail_failures] VALUES (getdate, @@spid, @From, @To, @Subject, @Body, @iMsg, @hr, @source, @description, @output, 'sp_OAGetErrorInfo for sp_OACreate') RETURN END ELSE BEGIN PRINT ' sp_OAGetErrorInfo failed.' RETURN END END --***************Configuring the Message Object ****************** -- This is to configure a remote SMTP server. -- http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cdosys/html/_cdosys_schema_configuration_sendusing.asp EXEC @hr = sp_OASetProperty @iMsg, 'Configuration.fields(&quot;http://schemas.microsoft.com/cdo/configuration/sendusing&quot;).Value','2' IF @hr <>0 BEGIN SELECT @hr INSERT INTO [dbo].[cdosysmail_failures] VALUES (getdate, @@spid, @From, @To, @Subject, @Body, @iMsg, @hr, @source, @description, @output, 'Failed at sp_OASetProperty sendusing') EXEC @hr = sp_OAGetErrorInfo NULL, @source OUT, @description OUT IF @hr = 0 BEGIN SELECT @output = ' Source: ' + @source PRINT @output SELECT @output = ' Description: ' + @description PRINT @output INSERT INTO [dbo].[cdosysmail_failures] VALUES (getdate, @@spid, @From, @To, @Subject, @Body, @iMsg, @hr, @source, @description, @output, 'sp_OAGetErrorInfo for sp_OASetProperty sendusing') GOTO send_cdosysmail_cleanup END ELSE BEGIN PRINT ' sp_OAGetErrorInfo failed.' GOTO send_cdosysmail_cleanup END END -- This is to configure the Server Name or IP address. -- Replace MailServerName by the name or IP of your SMTP Server. EXEC @hr = sp_OASetProperty @iMsg, 'Configuration.fields(&quot;http://schemas.microsoft.com/cdo/configuration/smtpserver&quot;).Value', cdoSMTPServerName IF @hr <>0 BEGIN SELECT @hr INSERT INTO [dbo].[cdosysmail_failures] VALUES (getdate, @@spid, @From, @To, @Subject, @Body, @iMsg, @hr, @source, @description, @output, 'Failed at sp_OASetProperty smtpserver') EXEC @hr = sp_OAGetErrorInfo NULL, @source OUT, @description OUT IF @hr = 0 BEGIN SELECT @output = ' Source: ' + @source PRINT @output SELECT @output = ' Description: ' + @description PRINT @output INSERT INTO [dbo].[cdosysmail_failures] VALUES (getdate, @@spid, @From, @To, @Subject, @Body, @iMsg, @hr, @source, @description, @output, 'sp_OAGetErrorInfo for sp_OASetProperty smtpserver') GOTO send_cdosysmail_cleanup END ELSE BEGIN PRINT ' sp_OAGetErrorInfo failed.' GOTO send_cdosysmail_cleanup END END -- Save the configurations to the message object. EXEC @hr = sp_OAMethod @iMsg, 'Configuration.Fields.Update', null IF @hr <>0 BEGIN SELECT @hr INSERT INTO [dbo].[cdosysmail_failures] VALUES (getdate, @@spid, @From, @To, @Subject, @Body, @iMsg, @hr, @source, @description, @output, 'Failed at sp_OASetProperty Update') EXEC @hr = sp_OAGetErrorInfo NULL, @source OUT, @description OUT IF @hr = 0 BEGIN SELECT @output = ' Source: ' + @source PRINT @output SELECT @output = ' Description: ' + @description PRINT @output INSERT INTO [dbo].[cdosysmail_failures] VALUES (getdate, @@spid, @From, @To, @Subject, @Body, @iMsg, @hr, @source, @description, @output, 'sp_OAGetErrorInfo for sp_OASetProperty Update') GOTO send_cdosysmail_cleanup END ELSE BEGIN PRINT ' sp_OAGetErrorInfo failed.' GOTO send_cdosysmail_cleanup END END -- Set the e-mail parameters. EXEC @hr = sp_OASetProperty @iMsg, 'To', @To IF @hr <>0 BEGIN SELECT @hr INSERT INTO [dbo].[cdosysmail_failures] VALUES (getdate, @@spid, @From, @To, @Subject, @Body, @iMsg, @hr, @source, @description, @output, 'Failed at sp_OASetProperty To') EXEC @hr = sp_OAGetErrorInfo NULL, @source OUT, @description OUT IF @hr = 0 BEGIN SELECT @output = ' Source: ' + @source PRINT @output SELECT @output = ' Description: ' + @description PRINT @output INSERT INTO [dbo].[cdosysmail_failures] VALUES (getdate, @@spid, @From, @To, @Subject, @Body, @iMsg, @hr, @source, @description, @output, 'sp_OAGetErrorInfo for sp_OASetProperty To') GOTO send_cdosysmail_cleanup END ELSE BEGIN PRINT ' sp_OAGetErrorInfo failed.' GOTO send_cdosysmail_cleanup END END

EXEC @hr = sp_OASetProperty @iMsg, 'From', @From IF @hr <>0 BEGIN SELECT @hr INSERT INTO [dbo].[cdosysmail_failures] VALUES (getdate, @@spid, @From, @To, @Subject, @Body, @iMsg, @hr, @source, @description, @output, 'Failed at sp_OASetProperty From') EXEC @hr = sp_OAGetErrorInfo NULL, @source OUT, @description OUT IF @hr = 0 BEGIN SELECT @output = ' Source: ' + @source PRINT @output SELECT @output = ' Description: ' + @description PRINT @output INSERT INTO [dbo].[cdosysmail_failures] VALUES (getdate, @@spid, @From, @To, @Subject, @Body, @iMsg, @hr, @source, @description, @output, 'sp_OAGetErrorInfo for sp_OASetProperty From') GOTO send_cdosysmail_cleanup END ELSE BEGIN PRINT ' sp_OAGetErrorInfo failed.' GOTO send_cdosysmail_cleanup END END

EXEC @hr = sp_OASetProperty @iMsg, 'Subject', @Subject IF @hr <>0 BEGIN SELECT @hr INSERT INTO [dbo].[cdosysmail_failures] VALUES (getdate, @@spid, @From, @To, @Subject, @Body, @iMsg, @hr, @source, @description, @output, 'Failed at sp_OASetProperty Subject') EXEC @hr = sp_OAGetErrorInfo NULL, @source OUT, @description OUT IF @hr = 0 BEGIN SELECT @output = ' Source: ' + @source PRINT @output SELECT @output = ' Description: ' + @description PRINT @output INSERT INTO [dbo].[cdosysmail_failures] VALUES (getdate, @@spid, @From, @To, @Subject, @Body, @iMsg, @hr, @source, @description, @output, 'sp_OAGetErrorInfo for sp_OASetProperty Subject') GOTO send_cdosysmail_cleanup END ELSE BEGIN PRINT ' sp_OAGetErrorInfo failed.' GOTO send_cdosysmail_cleanup END END -- If you are using HTML e-mail, use 'HTMLBody' instead of 'TextBody'. EXEC @hr = sp_OASetProperty @iMsg, 'TextBody', @Body IF @hr <>0 BEGIN SELECT @hr INSERT INTO [dbo].[cdosysmail_failures] VALUES (getdate, @@spid, @From, @To, @Subject, @Body, @iMsg, @hr, @source, @description, @output, 'Failed at sp_OASetProperty TextBody') EXEC @hr = sp_OAGetErrorInfo NULL, @source OUT, @description OUT IF @hr = 0 BEGIN SELECT @output = ' Source: ' + @source PRINT @output SELECT @output = ' Description: ' + @description PRINT @output INSERT INTO [dbo].[cdosysmail_failures] VALUES (getdate, @@spid, @From, @To, @Subject, @Body, @iMsg, @hr, @source, @description, @output, 'sp_OAGetErrorInfo for sp_OASetProperty TextBody') GOTO send_cdosysmail_cleanup END ELSE BEGIN PRINT ' sp_OAGetErrorInfo failed.' GOTO send_cdosysmail_cleanup END END

EXEC @hr = sp_OAMethod @iMsg, 'Send', NULL IF @hr <>0 BEGIN SELECT @hr INSERT INTO [dbo].[cdosysmail_failures] VALUES (getdate, @@spid, @From, @To, @Subject, @Body, @iMsg, @hr, @source, @description, @output, 'Failed at sp_OAMethod Send') EXEC @hr = sp_OAGetErrorInfo NULL, @source OUT, @description OUT IF @hr = 0 BEGIN SELECT @output = ' Source: ' + @source PRINT @output SELECT @output = ' Description: ' + @description PRINT @output INSERT INTO [dbo].[cdosysmail_failures] VALUES (getdate, @@spid, @From, @To, @Subject, @Body, @iMsg, @hr, @source, @description, @output, 'sp_OAGetErrorInfo for sp_OAMethod Send') GOTO send_cdosysmail_cleanup END ELSE BEGIN PRINT ' sp_OAGetErrorInfo failed.' GOTO send_cdosysmail_cleanup END END

-- Do some error handling after each step if you have to. -- Clean up the objects created. send_cdosysmail_cleanup: If (@iMsg IS NOT NULL) -- if @iMsg is NOT NULL then destroy it   BEGIN EXEC @hr=sp_OADestroy @iMsg -- handle the failure of the destroy if needed IF @hr <>0 BEGIN select @hr INSERT INTO [dbo].[cdosysmail_failures] VALUES (getdate, @@spid, @From, @To, @Subject, @Body, @iMsg, @hr, @source, @description, @output, 'Failed at sp_OADestroy') EXEC @hr = sp_OAGetErrorInfo NULL, @source OUT, @description OUT -- if sp_OAGetErrorInfo was successful, print errors IF @hr = 0 BEGIN SELECT @output = ' Source: ' + @source PRINT @output SELECT @output = ' Description: ' + @description PRINT @output INSERT INTO [dbo].[cdosysmail_failures] VALUES (getdate, @@spid, @From, @To, @Subject, @Body, @iMsg, @hr, @source, @description, @output, 'sp_OAGetErrorInfo for sp_OADestroy') END -- else sp_OAGetErrorInfo failed ELSE BEGIN PRINT ' sp_OAGetErrorInfo failed.' RETURN END END END ELSE BEGIN PRINT ' sp_OADestroy skipped because @iMsg is NULL.' INSERT INTO [dbo].[cdosysmail_failures] VALUES (getdate, @@spid, @From, @To, @Subject, @Body, @iMsg, @hr, @source, @description, @output, '@iMsg is NULL, sp_OADestroy skipped') RETURN END Next, use the stored procedure that you created and provide the correct parameters.

declare @Body varchar(4000) select @Body = 'This is a Test Message' exec sp_send_cdosysmail 'someone@example.com','someone2@example.com','Test of CDOSYS',@Body Note Only members of the sysadmin fixed server role can run the OLE Automation stored procedures. If your 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 mail with CDOSYS. For example, you can use a Microsoft Visual Basic application.

CDOSYS sends the e-mail to the remote SMTP mail server that is specified as cdoSMTPServerName.

The SMTP mail server sends the mail to the e-mail address that is specified in the To: argument (&quot;someone2@example.com&quot; in this example). The name that is specified in the From: argument appears as the sender of the e-mail (&quot;someone@example.com&quot; in this example).

You can modify this example to have it send text-based e-mail or attachments. For documentation about CDOSYS, visit the following Microsoft Web site:

http://msdn.microsoft.com/library

In the left pane, expand Messaging and Collaboration, expand Collaboration Data Objects, and then expand CDO for Windows 2000.

back to the top



MORE INFORMATION
We strongly suggest that you send e-mail by using the local SMTP pickup directory instead of using the remote SMTP mail name. The sp_send_cdosysmail sample stored procedure runs in process with SQL Server and will fail if the SMTP server is offline or broken. Sending e-mail through the local pickup directory only requires that the stored procedure has permission to the pickup directory. The SMTP server will independently handle the mail.

For more information about how to send mail by using the local pickup directory, click the following article number to view the article in the Microsoft Knowledge Base:

286430 How to send HTML formatted mail using CDO for Windows 2000 and the local pickup directory

