Database Mail, Part 1

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.

   1: /**************************************************

   2: Name: dbo.DatabaseMail_Enable

   3: Written by: Al Eardley

   4: Date: December 2010

   5: Purpose:

   6: This script creates a stored procedure that enables Database Mail

   7:  

   8: Parameters:

   9:     @aSPMode - Not currently used

  10:  

  11: Execution:

  12: DECLARE @rv int

  13: EXEC @rv = dbo.DatabaseMail_Enable

  14: PRINT @rv

  15:  

  16: **************************************************/

  17:

  18: /**************************************************

  19: ** Initialise Script

  20: **************************************************/

  21: PRINT 'dbo.DatabaseMail_Enable';

  22:

  23: SET ANSI_NULLS ON;

  24: SET NOCOUNT ON;

  25:

  26: /**************************************************

  27: ** Delete SP

  28: **************************************************/

  29: IF OBJECT_ID('dbo.DatabaseMail_Enable') IS NOT NULL

  30: BEGIN

  31:     PRINT CHAR(9) + 'dropping';

  32:     DROP PROCEDURE dbo.DatabaseMail_Enable;

  33: END

  34: GO

  35:

  36: /**************************************************

  37: ** Create SP

  38: **************************************************/

  39: CREATE PROCEDURE dbo.DatabaseMail_Enable (

  40:     @aSPMode    INT     = 0

  41: ) AS

  42:

  43: SET NOCOUNT ON;

  44: SET QUOTED_IDENTIFIER ON;

  45:

  46: /**************************************************

  47: Declare default variables

  48: **************************************************/

  49: DECLARE

  50:     @ErrorID                INT                = 0

  51: ,    @ErrorDesc                NVARCHAR(256)    = 'No Error'

  52: ,    @ReturnValue            INT                = 0

  53: ,    @Rowcount                INT                = 0

  54: ,    @SPName                     NVARCHAR(128)    = 'dbo.DatabaseMail_Enable'

  55: ,    @CodeBlockDesc            NVARCHAR(256)    = ''

  56: ,    @CalledSPReturnValue    INT                = 0

  57: ;

  58:

  59: /**************************************************

  60: Declare local variables

  61: **************************************************/

  62: DECLARE

  63:     @ShowAdvancedOptionsName        VARCHAR(35)    = 'show advanced options'

  64: ,    @ShowAdvancedOptionsValue        INT

  65: ,    @DatabaseMailXPsName            VARCHAR(35)    = 'Database Mail XPs'

  66: ;

  67:

  68: /**************************************************/

  69: SET @CodeBlockDesc = 'Get the existing value of ''show advanced options'''

  70: /**************************************************/

  71: BEGIN TRY

  72:     SELECT

  73:         @ShowAdvancedOptionsValue = CAST(C.value AS INT)

  74:     FROM sys.configurations C

  75:     WHERE

  76:         C.name = @ShowAdvancedOptionsName

  77:     ;

  78:

  79:     IF @@rowcount = 0

  80:     BEGIN

  81:         SET @ErrorDesc        = 'No rows affected: ' + @CodeBlockDesc;

  82:         SET @ReturnValue    = -1;

  83:         GOTO ErrorHandler;

  84:     END

  85: END TRY

  86: BEGIN CATCH

  87:     SET @ErrorDesc        = 'Fatal Error: ' + @CodeBlockDesc + ': ' + CAST(ERROR_LINE() AS nvarchar) + ' - ' + CAST(ERROR_NUMBER() AS nvarchar) + ' - ' + ERROR_MESSAGE();

  88:     SET @ReturnValue    = -1;

  89:     GOTO ErrorHandler;

  90: END CATCH

  91:

  92: /**************************************************/

  93: SET @CodeBlockDesc = 'Showing advanced options';

  94: /**************************************************/

  95: IF (@ShowAdvancedOptionsValue != 1)

  96: BEGIN

  97:     BEGIN TRY

  98:         PRINT @CodeBlockDesc;

  99:

 100:         EXEC @CalledSPReturnValue = sp_configure @ShowAdvancedOptionsName, 1;

 101:

 102:         IF @CalledSPReturnValue != 0

 103:         BEGIN

 104:             SET @ErrorDesc        = 'Hard Error: ' + @CodeBlockDesc + ': RV=' + CAST(@CalledSPReturnValue AS varchar);

 105:             SET @ReturnValue    = @CalledSPReturnValue;

 106:             GOTO ErrorHandler;

 107:         END

 108:

 109:         RECONFIGURE;

 110:     END TRY

 111:     BEGIN CATCH

 112:         SET @ErrorDesc        = 'Fatal Error: ' + @CodeBlockDesc + ': ' + CAST(ERROR_LINE() AS nvarchar) + ' - ' + CAST(ERROR_NUMBER() AS nvarchar) + ' - ' + ERROR_MESSAGE();

 113:         SET @ReturnValue    = -1;

 114:         GOTO ErrorHandler;

 115:     END CATCH

 116: END

 117:

 118: /**************************************************/

 119: SET @CodeBlockDesc = 'Enabling Database Mail XPs';

 120: /**************************************************/

 121: IF ((SELECT TOP(1)

 122:             C.value

 123:         FROM sys.configurations C

 124:         WHERE

 125:             C.name = @DatabaseMailXPsName

 126:     ) != 1)

 127: BEGIN

 128:     BEGIN TRY

 129:         PRINT @CodeBlockDesc;

 130:

 131:         EXEC @CalledSPReturnValue = sp_configure @DatabaseMailXPsName, 1;

 132:

 133:         IF @CalledSPReturnValue != 0

 134:         BEGIN

 135:             SET    @ErrorDesc        = 'Hard Error: ' + @CodeBlockDesc + ': RV=' + CAST(@CalledSPReturnValue AS varchar);

 136:             SET @ReturnValue    = @CalledSPReturnValue;

 137:             GOTO ErrorHandler;

 138:         END

 139:

 140:         RECONFIGURE;

 141:     END TRY

 142:     BEGIN CATCH

 143:         SET @ErrorDesc        = 'Fatal Error: ' + @CodeBlockDesc + ': ' + CAST(ERROR_LINE() AS nvarchar) + ' - ' + CAST(ERROR_NUMBER() AS nvarchar) + ' - ' + ERROR_MESSAGE();

 144:         SET @ReturnValue    = -1;

 145:         GOTO ErrorHandler;

 146:     END CATCH

 147: END

 148:

 149: /**************************************************/

 150: SET @CodeBlockDesc = 'Starting the Service Broker objects that the external program uses';

 151: /**************************************************/

 152: BEGIN TRY

 153:     PRINT @CodeBlockDesc;

 154:

 155:     EXEC @CalledSPReturnValue = msdb..sysmail_start_sp;

 156:

 157:     IF @CalledSPReturnValue != 0

 158:     BEGIN

 159:         SET    @ErrorDesc        = 'Hard Error: ' + @CodeBlockDesc + ': RV=' + CAST(@CalledSPReturnValue AS varchar);

 160:         SET @ReturnValue    = @CalledSPReturnValue;

 161:         GOTO ErrorHandler;

 162:     END

 163: END TRY

 164: BEGIN CATCH

 165:     SET @ErrorDesc        = 'Fatal Error: ' + @CodeBlockDesc + ': ' + CAST(ERROR_LINE() AS nvarchar) + ' - ' + CAST(ERROR_NUMBER() AS nvarchar) + ' - ' + ERROR_MESSAGE();

 166:     SET @ReturnValue    = -1;

 167:     GOTO ErrorHandler;

 168: END CATCH

 169:

 170:

 171: /**************************************************/

 172: SET @CodeBlockDesc = 'Hiding advanced options';

 173: /**************************************************/

 174: IF (@ShowAdvancedOptionsValue = 0)

 175: BEGIN

 176:     BEGIN TRY

 177:         PRINT @CodeBlockDesc;

 178:

 179:         EXEC @CalledSPReturnValue = sp_configure @ShowAdvancedOptionsName, 0;

 180:

 181:         IF @CalledSPReturnValue != 0

 182:         BEGIN

 183:             SET    @ErrorDesc        = 'Hard Error: ' + @CodeBlockDesc + ': RV=' + CAST(@CalledSPReturnValue AS varchar);

 184:             SET @ReturnValue    = @CalledSPReturnValue;

 185:             GOTO ErrorHandler;

 186:         END

 187:

 188:         RECONFIGURE;

 189:     END TRY

 190:     BEGIN CATCH

 191:         SET @ErrorDesc        = 'Fatal Error: ' + @CodeBlockDesc + ': ' + CAST(ERROR_LINE() AS nvarchar) + ' - ' + CAST(ERROR_NUMBER() AS nvarchar) + ' - ' + ERROR_MESSAGE();

 192:         SET @ReturnValue    = -1;

 193:         GOTO ErrorHandler;

 194:     END CATCH

 195: END

 196:

 197: /**************************************************

 198: Success

 199: **************************************************/

 200: RETURN @ReturnValue;

 201:

 202: /**************************************************/

 203: ErrorHandler:

 204: /**************************************************/

 205: PRINT @ErrorDesc;

 206:

 207: /**************************************************

 208: Return

 209: **************************************************/

 210: RETURN @ReturnValue;

 211:

 212: GO

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

Leave a Reply

Your email address will not be published.