In this article I have explained how to send email from database using stored procedure in SQL Server. This technique makes use of Collaboration Data Objects (CDO) for sending email messages which works in most SQL Server versions 2000, 2008, 2008R2 and 2012. Many times it is needed to send a email from the database. The important reason is that you do not need to pull the data in front end and then send emails from front end.

Also if the database server and application server are separate, it takes of the load from the application server.

 

Collaboration Data Objects (CDO)

For sending emails through SMTP Server I will be using Collaboration Data Objects (CDO).

CDO are part of Windows and are useful in sending SMTP Emails.

For more information on CDO Read here.

In SQL Server 2000, I’ll create a stored procedure that will be used to send emails using CDO.

I’ll explain how to send emails using GMAIL SMTP Server.

Here I have created a stored procedure sp_send_cdosysmail which accepts the following parameters

 

Parameter

Relevance

@from

Email Address of the Sender

@to

Email Address of the Recipient

@subject

Subject of the Email

@body

Body of the Email

@bodytype

Type of Body (Text or HTML)

@output_mesg

Output parameter that returns the status (Success / Failed)

@output_desc

Output parameter that returns the Error description if an error occurs

GO

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

 

 

CREATE PROCEDURE [dbo].[sp_send_mail]

            @from varchar(500) ,

            @to varchar(500) ,

            @subject varchar(500),

            @body varchar(4000) ,

            @bodytype varchar(10),

            @output_mesg varchar(10) output,

            @output_desc varchar(1000) output

AS

DECLARE @imsg int

DECLARE @hr int

DECLARE @source varchar(255)

DECLARE @description varchar(500)

In the above SQL Snippet I have created the stored procedure and declared some variables that will be used later.

 

Create an OLE Instance of CDO

EXEC @hr = sp_oacreate 'cdo.message', @imsg out

 

SendUsing

SendUsing Specifies Whether to send using port (2) or using pickup directory (1)

EXEC @hr = sp_oasetproperty @imsg,'configuration.fields("http://schemas.microsoft.com/cdo/configuration/sendusing").value','2'


 

smtpserver

Specify your SMTP Server that you will use. Here I am using gmail SMTP Server.

EXEC @hr = sp_oasetproperty @imsg,

'configuration.fields("http://schemas.microsoft.com/cdo/configuration/smtpserver").value', 'smtp.gmail.com'

 

 

sendusername

Specify the sender’s email address here. The account that will be used to send emails.

EXEC @hr = sp_oasetproperty @imsg, 'configuration.fields("http://schemas.microsoft.com/cdo/configuration/sendusername").value', '<Gmail Email>'

 

sendpassword

Specify the password of the account here.

EXEC @hr = sp_oasetproperty @imsg,

'configuration.fields("http://schemas.microsoft.com/cdo/configuration/sendpassword").value', '<Gmail Password>'

 

smtpusessl

Specify where the SMTP server requires SSL (True) or not (False)

EXEC @hr = sp_oasetproperty @imsg,

'configuration.fields("http://schemas.microsoft.com/cdo/configuration/smtpusessl").value', 'True'

 

smtpserverport

Specify the Port Number foy your SMTP Server (465 or 587)

EXEC @hr = sp_oasetproperty @imsg,

'configuration.fields("http://schemas.microsoft.com/cdo/configuration/smtpserverport").value', '587'

 

 

 

smtpauthenticate

Specify the Type of Authentication Required None (0) / Basic (1) 

EXEC @hr = sp_oasetproperty @imsg,

'configuration.fields("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate").value', '1'

 

 

Send Email

Execute the OLE object to send email

EXEC @hr = sp_oamethod @imsg, 'configuration.fields.update', null

EXEC @hr = sp_oasetproperty @imsg, 'to', @to

EXEC @hr = sp_oasetproperty @imsg, 'from', @from

EXEC @hr = sp_oasetproperty @imsg, 'subject', @subject

EXEC @hr = sp_oasetproperty @imsg, @bodytype, @body

EXEC @hr = sp_oamethod @imsg, 'send', null

 

 

Error Handling

Below snippet is checking if the mail is send successfully. If not it captures the Error message and the

Error Description in the output variables

SET @output_mesg = 'Success'

IF @hr <>0

      SELECT @hr

      BEGIN

            EXEC @hr = sp_oageterrorinfo null, @source out, @description out

            IF @hr = 0

            BEGIN

                  set @output_desc =  @description

            END

      ELSE

      BEGIN

            SET @output_desc = ' sp_oageterrorinfo failed'

      END

      IF not @output_desc is NULL

                  SET @output_mesg = 'Error'

END

 

Destroy the OLE Object Instance

EXEC @hr = sp_oadestroy @imsg

 

 

Calling and Execute the Stored Procedure

Below I am calling the Stored Procedure and passing the parameters.

Note: the Bodytype can be HTML (htmlbody) or Text (textbody)

DECLARE @out_desc varchar(1000),

        @out_mesg varchar(10)

 

EXEC sp_send_mail 'sender@gmail.com',

      'receiver@gmail.com',

      'Hello',

      '<b>This is s Test Mail</b>',

      'htmlbody', @output_mesg = @out_mesg output,

      @output_desc = @out_desc output

 

PRINT @out_mesg

PRINT @out_desc

 

 

Enable OLE Automation in SQL Server

OLE Automation is disabled by default in SQL Server hence to make this stored procedure work you will need to run the following script.

sp_configure 'show advanced options', 1;

GO

RECONFIGURE;

GO

sp_configure 'Ole Automation Procedures', 1;

GO

RECONFIGURE;

GO

 

Downloads

Download Code (1.79 kb)