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', sender@gmail.com'


 

sendpassword

 

Specify the password of the account here.

 

EXEC @hr = sp_oasetproperty @imsg,

'configuration.fields("http://schemas.microsoft.com/cdo/configuration/

sendpassword").value', 'xxxxxxxxxxx'


 

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 2005

 

OLE Automation is disabled by default in SQL Server 2005 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


 

You can download the complete script here.

Download Code (1.79 kb)