Sending SMTP Mail using a Stored Procedure

By Damian Maclennen on 19 July 2001 | Tags: SQLMail

One of the downsides of SQL Mail is the fact that it is tied to a MAPI Profile. Here is a way to send email using the sp_OACreate procedure to call a third party SMTP component.

Sending email through SQL Server is very cool as well as very handy. However the SQL Mail service provided with SQL Server can be restrictive. SQL Mail uses a MAPI profile on the server, but this ties you to one profile and requires you to have a mail client like Outlook installed on your server.

This doesn't help you much if you want to be able to send from different addresses, or if you are unable to set up a MAPI profile on the server. There is a lot of call for using SQL Mail to use just an SMTP server, unfortunatly we just can't do it.....but there is an alternative.

SQL Server gives us a few stored procedures for calling and using COM Components. With so many SMTP COM objects avaiable for use in ASP pages it makes sense to combine the two.

sp_SMTPMail is a stored procedure that calls a the ASP Mail COM object from www.serverobjects.com to send an email. This is easily modifiable to use any mail component. JMail from tech.dimac.net is another good one that I use, but ASP Mail is what I used on this day.

To call the COM object I use a few system stored procedures. sp_OACreate which creates an instance of a COM object, sp_OASetProperty to set properties of the object, and sp_Method to call methods of the object.

Here is the procedure. I have broken it up to go through it piece by piece, the full source code is linked at the bottom of the page

Create Procedure sp_SMTPMail

	@SenderName varchar(100),
	@SenderAddress varchar(100),
	@RecipientName varchar(100),
	@RecipientAddress varchar(100),
	@Subject varchar(200),
	@Body varchar(8000),
	@MailServer varchar(100) = 'localhost'

	AS	
	
	SET nocount on

No surprises here. Set up the procedure and the parameters. I have set up the SMTP mailserver parameter to default to the local machine.

	declare @oMail int --Object reference
	declare @resultcode int
	
	EXEC @resultcode = sp_OACreate 'SMTPsvg.Mailer', @oMail OUT

sp_OACreate has an output parameter that returns a reference to the object instance, this is used to assign parameters. The result code is 0 for success.

	if @resultcode = 0
	BEGIN
		EXEC @resultcode = sp_OASetProperty @oMail, 'RemoteHost',  @mailserver
		EXEC @resultcode = sp_OASetProperty @oMail, 'FromName', @SenderName
		EXEC @resultcode = sp_OASetProperty @oMail, 'FromAddress',  @SenderAddress

sp_OASetProperty takes the reference to the object (@oMail), takes the property name and sets the value.

EXEC @resultcode = sp_OAMethod @oMail, 'AddRecipient', NULL, @RecipientName,  @RecipientAddress

sp_OAMethod calls a method of the object. We pass the object reference, then the method name "AddRecipient". The next parameter is for returning a value from a method. In this case I don't want one so I pass it a null. After that I pass the parameters of the method. The "AddRecipient" method has Name and Email Address parameters so I am passing them in.

		EXEC @resultcode = sp_OASetProperty @oMail, 'Subject', @Subject
		EXEC @resultcode = sp_OASetProperty @oMail, 'BodyText', @Body


		EXEC @resultcode = sp_OAMethod @oMail, 'SendMail', NULL

Similar code here, we set the subject and message body, then call the "SendMail" method which sends the email.

		EXEC sp_OADestroy @oMail
	END	
	

	SET nocount off

sp_OADestory cleans up and destroys the reference to the object. The object is meant to be destroyed when the procedure finishes, but I always like to clean up after myself.

To use this procedure, call it like this.

exec sp_SMTPMail @SenderName='me', @SenderAddress='me@somewhere.com', 
@RecipientName = 'Someone', @RecipientAddress = 'someone@someplace.com', 
@Subject='SQL Test', @body='Hello, this is a test email from SQL Server'

That wraps up the procedure which will send mail via an SMTP server. It is not an extremely quick technique to call an SMTP component like this, although using a queuing component like ASP Queue Mail (A companion to ASP Mail) will speed it up. But it is a good way to get around the limitations of SQL Mail. It also shows that with a bit of creative thinking you can get around limitations presented to you. In my next article I will show you a way to build a queuing message system that will be quicker as well as get around the limitation of the 8000 character parameter limit.

Until then. Have fun with it.

Full procedure source code Here