ANSMTP Developers Center > Using ANSMTP in MS SQL Server

Note: this article is only suitable for ANSMTP 6.4 or later.
For ANSMTP 6.3 or earlier version, please click here.

Introduction

ANSMTP is a SMTP component which supports all operations of SMTP/ESMTP protocols (RFC 821, RFC 822, RFC 2554). This tutorial covers the basics of sending email with ANSMTP in MS SQL stored procedure.

Installation and Deployment

You should download the ansmtp installer and install it on your machine at first. If you want to distribute or deploy ansmtp without ansmtp installer, please click here to learn more.

How does it work?

MS SQL Server 2000 provides several system Stored Procedures for invoking COM object.

sp_OACreate
Creates an instance of the COM object on an instance of MS SQL Server
					
sp_OADestroy
Destroys a created COM object
					
sp_OAGetProperty
Gets a property value of a COM object.
					
sp_OASetProperty
Sets a property value of a COM object to a new value. 
					
sp_OAMethod
Calls a method of a COM object.
					

More detail about these Stored Procedures, please refer to MS SQL Server documentation. We will demonstrates how to use these Stored Procedure to send email in the following example.

Usage Example

The following code demonstrates how to send email in Stored Procedure.

CREATE PROCEDURE [dbo].[sp_SendEmail]  @ServerAddr varchar(80), 
@FromAddr varchar(80), 
@Recipient varchar(80), 
@Subject varchar(132),
@BodyText varchar(2000),
@nRet int OUTPUT

AS
DECLARE @hr int
DECLARE @oSmtp int
 
EXEC @hr = sp_OACreate 'AOSMTP.Mail',@oSmtp OUT
EXEC @hr = sp_OASetProperty @oSmtp, 'RegisterKey', 'replace this text by your key'
EXEC @hr = sp_OASetProperty @oSmtp, 'ServerAddr', @ServerAddr
EXEC @hr = sp_OASetProperty @oSmtp, 'FromAddr', @FromAddr
EXEC @hr = sp_OAMethod @oSmtp, 'AddRecipient', NULL, @Recipient, @Recipient, 0 
EXEC @hr = sp_OASetProperty @oSmtp, 'Subject', @Subject 
EXEC @hr = sp_OASetProperty @oSmtp, 'BodyText', @BodyText 
 
EXEC @hr = sp_OAMethod @oSmtp, 'SendMail', @nRet OUT 
EXEC @hr = sp_OADestroy @oSmtp

-- @nRet is zero if succeeded, otherwise @nRet is non-zero.
GO

Now you can invoke this Stored Procedure in any programming language to send email out.

Related Links

Email Queuing with Database
Email Queueing with MSMQ
IIS SMTP Service

Free Email Support

Not enough? Please contact our technical support team.

Support@EmailArchitect.NET
VIP@EmailArchitect.NET(Registered User)

Remarks
We usually reply emails in 24hours. The reason for getting no response is likely that your smtp server bounced our reply. In this case, please try to use another email address to contact us. Your Hotmail or Yahoo email account is recommended.