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
Comment on this post: