Sending Mails in SQL Server

SQL Code Snippets to send email

Below are snippets which can be used to send emails from SQL Server
Note: you need to set the email parameters like the SMTP server e.t.c. wherever mentioned.




The second snippet uses CDONTS and the third uses CDOSYS objects










-----------------------------------------------------------------------------------------


--Start SP # 1 Send Mail through SQL

CREATE PROCEDURE usp_SMTPMail
    @SenderName varchar(100),
    @SenderAddress varchar(100),
    @RecipientName varchar(100),
    @RecipientAddress varchar(100),
    @Subject varchar(200),
    @Body varchar(8000),
    @MailServer varchar(100) = '' --Specify the smtpserver with domain here
AS    
SET nocount on
--Set up the procedure and the parameters.
    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 usp_SMTPMail @SenderName='
me', @SenderAddress='dummyuser@gmail.com',
@RecipientName = '
dummyuserreceipient@gmail.com', @RecipientAddress = 'dummyuserreceipient@gmail.com',
@Subject='
SQL Test', @body='Hello, this is a test email from SQL Server'

--End SP # 1 Send Mail through SQL
-----------------------------------------------------------------------------------------

-----------------------------------------------------------------------------------------

--Start SP # 2 Send Mail through SQL
CREATE PROCEDURE [dbo].[usp_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

exec usp_send_cdontsmail '
'dummyuserreceipient@gmail.com',''dummyuserreceipient@gmail.com','Test of CDONTS','It works'

--End SP # 2 Send Mail through SQL

-----------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------

--Start SP # 3 Send Mail through SQL

-- 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)=" ",
       @Body varchar(4000) =" "
    /*********************************************************************

    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("http://schemas.microsoft.com/cdo/configuration/sendusing").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("http://schemas.microsoft.com/cdo/configuration/smtpserver").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 '
'dummyuserreceipient@gmail.com',''dummyuserreceipient@gmail.com','Test of CDOSYS',@Body

--End SP # 3 Send Mail through SQL

-----------------------------------------------------------------------------------------
By [)ia6l0 iii   Popularity  (3498 Views)