Database Mail, Part 1 - Set up

14 Dec 2010 4-minute read Al Eardley
Data PlatformOn-Prem
SQL ServerT-SQL

Over the years I have come across many reasons for Database Mail to be activated, and a couple of reasons not to use it.

The main reasons for activating it are the added flexibility that can be achieved in reporting the success or failure of maintenance jobs or any unattended processes and for monitoring and notification purposes.

As a business tool for sending e-mail, it is sufficient for small numbers of e-mails but I would always recommend using an application that produces a well-formed e-mail with all of the appropriate attributes rather than using Database Mail.

In this first part I will demonstrate setting up Database Mail in SQL Server 2008 and configuring it. Rather than using SQL Management Studio, I will do this using scripts and stored procedures as this is a repeatable process and one that can carried out on multiple servers.

The procedure that I have written to enable Database Mail uses the sp_configure procedure to change the value of the configuration property ‘Database Mail XPs’ to 1 and then starts the Service Broker using msdb..sysmail_start_sp.

/**************************************************
Name: dbo.DatabaseMail_Enable
Written by: Al Eardley
Date: December 2010
Purpose:
This script creates a stored procedure that enables Database Mail

Parameters:
    @aSPMode - Not currently used

Execution:
DECLARE @rv int
EXEC @rv = dbo.DatabaseMail_Enable
PRINT @rv

**************************************************/

/**************************************************
** Initialise Script
**************************************************/
PRINT 'dbo.DatabaseMail_Enable';

ET ANSI_NULLS ON;
SET NOCOUNT ON;

/**************************************************
** Delete SP
**************************************************/
IF OBJECT_ID('dbo.DatabaseMail_Enable') IS NOT NULL
BEGIN
    PRINT CHAR(9) + 'dropping';
    DROP PROCEDURE dbo.DatabaseMail_Enable;
END
GO

/**************************************************
** Create SP
**************************************************/
CREATE PROCEDURE dbo.DatabaseMail_Enable (
    @aSPMode    INT     = 0
) AS

SET NOCOUNT ON;
SET QUOTED_IDENTIFIER ON;

/**************************************************
Declare default variables
**************************************************/
DECLARE
    @ErrorID                INT                = 0
,    @ErrorDesc                NVARCHAR(256)    = 'No Error'
,    @ReturnValue            INT                = 0
,    @Rowcount                INT                = 0
,    @SPName                     NVARCHAR(128)    = 'dbo.DatabaseMail_Enable'
,    @CodeBlockDesc            NVARCHAR(256)    = ''
,    @CalledSPReturnValue    INT                = 0
;

/**************************************************
Declare local variables
**************************************************/
DECLARE
    @ShowAdvancedOptionsName        VARCHAR(35)    = 'show advanced options'
,    @ShowAdvancedOptionsValue        INT
,    @DatabaseMailXPsName            VARCHAR(35)    = 'Database Mail XPs'
;

/**************************************************/
SET @CodeBlockDesc = 'Get the existing value of ''show advanced options'''
/**************************************************/
BEGIN TRY
    SELECT
        @ShowAdvancedOptionsValue = CAST(C.value AS INT)
    FROM sys.configurations C
    WHERE
        C.name = @ShowAdvancedOptionsName
    ;

    IF @@rowcount = 0
    BEGIN
        SET @ErrorDesc        = 'No rows affected: ' + @CodeBlockDesc;
        SET @ReturnValue    = -1;
        GOTO ErrorHandler;
    END
END TRY
BEGIN CATCH
    SET @ErrorDesc        = 'Fatal Error: ' + @CodeBlockDesc + ': ' + CAST(ERROR_LINE() AS nvarchar) + ' - ' + CAST(ERROR_NUMBER() AS nvarchar) + ' - ' + ERROR_MESSAGE();
    SET @ReturnValue    = -1;
    GOTO ErrorHandler;
END CATCH

/**************************************************/
SET @CodeBlockDesc = 'Showing advanced options';
/**************************************************/
IF (@ShowAdvancedOptionsValue != 1)
BEGIN
    BEGIN TRY
        PRINT @CodeBlockDesc;

        EXEC @CalledSPReturnValue = sp_configure @ShowAdvancedOptionsName, 1;

        IF @CalledSPReturnValue != 0
        BEGIN
            SET @ErrorDesc        = 'Hard Error: ' + @CodeBlockDesc + ': RV=' + CAST(@CalledSPReturnValue AS varchar);
            SET @ReturnValue    = @CalledSPReturnValue;
            GOTO ErrorHandler;
        END

        RECONFIGURE;
    END TRY
    BEGIN CATCH
        SET @ErrorDesc        = 'Fatal Error: ' + @CodeBlockDesc + ': ' + CAST(ERROR_LINE() AS nvarchar) + ' - ' + CAST(ERROR_NUMBER() AS nvarchar) + ' - ' + ERROR_MESSAGE();
        SET @ReturnValue    = -1;
        GOTO ErrorHandler;
    END CATCH
END

/**************************************************/
SET @CodeBlockDesc = 'Enabling Database Mail XPs';
/**************************************************/
IF ((SELECT TOP(1)
            C.value
        FROM sys.configurations C
        WHERE
            C.name = @DatabaseMailXPsName
    ) != 1)
BEGIN
    BEGIN TRY
        PRINT @CodeBlockDesc;

        EXEC @CalledSPReturnValue = sp_configure @DatabaseMailXPsName, 1;

        IF @CalledSPReturnValue != 0
        BEGIN
            SET    @ErrorDesc        = 'Hard Error: ' + @CodeBlockDesc + ': RV=' + CAST(@CalledSPReturnValue AS varchar);
            SET @ReturnValue    = @CalledSPReturnValue;
            GOTO ErrorHandler;
        END

        RECONFIGURE;
    END TRY
    BEGIN CATCH
        SET @ErrorDesc        = 'Fatal Error: ' + @CodeBlockDesc + ': ' + CAST(ERROR_LINE() AS nvarchar) + ' - ' + CAST(ERROR_NUMBER() AS nvarchar) + ' - ' + ERROR_MESSAGE();
        SET @ReturnValue    = -1;
        GOTO ErrorHandler;
    END CATCH
END

/**************************************************/
SET @CodeBlockDesc = 'Starting the Service Broker objects that the external program uses';
/**************************************************/
BEGIN TRY
    PRINT @CodeBlockDesc;

    EXEC @CalledSPReturnValue = msdb..sysmail_start_sp;

    IF @CalledSPReturnValue != 0
    BEGIN
        SET    @ErrorDesc        = 'Hard Error: ' + @CodeBlockDesc + ': RV=' + CAST(@CalledSPReturnValue AS varchar);
        SET @ReturnValue    = @CalledSPReturnValue;
        GOTO ErrorHandler;
    END
END TRY
BEGIN CATCH
    SET @ErrorDesc        = 'Fatal Error: ' + @CodeBlockDesc + ': ' + CAST(ERROR_LINE() AS nvarchar) + ' - ' + CAST(ERROR_NUMBER() AS nvarchar) + ' - ' + ERROR_MESSAGE();
    SET @ReturnValue    = -1;
    GOTO ErrorHandler;
END CATCH

/**************************************************/
SET @CodeBlockDesc = 'Hiding advanced options';
/**************************************************/
IF (@ShowAdvancedOptionsValue = 0)
BEGIN
    BEGIN TRY
        PRINT @CodeBlockDesc;

        EXEC @CalledSPReturnValue = sp_configure @ShowAdvancedOptionsName, 0;

        IF @CalledSPReturnValue != 0
        BEGIN
            SET    @ErrorDesc        = 'Hard Error: ' + @CodeBlockDesc + ': RV=' + CAST(@CalledSPReturnValue AS varchar);
            SET @ReturnValue    = @CalledSPReturnValue;
            GOTO ErrorHandler;
        END

        RECONFIGURE;
    END TRY
    BEGIN CATCH
        SET @ErrorDesc        = 'Fatal Error: ' + @CodeBlockDesc + ': ' + CAST(ERROR_LINE() AS nvarchar) + ' - ' + CAST(ERROR_NUMBER() AS nvarchar) + ' - ' + ERROR_MESSAGE();
        SET @ReturnValue    = -1;
        GOTO ErrorHandler;
    END CATCH
END

/**************************************************
Success
**************************************************/
RETURN @ReturnValue;

/**************************************************/
ErrorHandler:
/**************************************************/
PRINT @ErrorDesc;

/**************************************************
Return
**************************************************/
RETURN @ReturnValue;

GO

Next, how to create an account - Database Mail, Part 2 - Creating an Account

Posts in this Series


Comment on this post: