Database Mail, Part 2 - Creating an Account

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

In Database Mail, Part 1 - Set up I produced a stored procedure that enables Database Mail. The second stage is to create an account which determines the e-mail address that messages will be sent from and the SMTP server that will be used to send the e-mails.

It is worth noting that if your organisation has primary and secondary SMTP servers then accounts can be set up for both of the servers, or as many as your organisation has.

This procedure requires the following parameters to be populated:

Account Name The name of the account. This is used to identify the account when creating a relationship with the profile in Part 3 of this series.
Description This is a description of the account and should include the name of the SMTP server that is being used to send the e-mail.
SMTP Server Name The name of the SMTP server

My procedure assumes that no authentication is required to access the SMTP server and that the default port number of 25 is used to access the server. If your environment requires authentication then then there are other parameters that will allow you to configure a user name and password, change the port and enable SSL.

/**************************************************
Name: dbo.DatabaseMail_CreateAccount
Written by: Al Eardley
Date: December 2010
Purpose:
This script creates a stored procedure that creates a Database Mail Account
Parameters:
    @aSPMode - Not currently used

Execution:
DECLARE @rv int
EXEC @rv = dbo.DatabaseMail_CreateAccount
     @aSPMode            = 0
,    @aAccountName        = 'TestAccount'
,    @aAccountDescription        = 'Test Account Description'
,    @aEmailAddress        = 'Administrator@localhost'
,    @aMailServerName    = 'localhost'

PRINT @rv
**************************************************/

/**************************************************/
PRINT 'dbo.DatabaseMail_CreateAccount';
/**************************************************/
SET ANSI_NULLS ON;
SET NOCOUNT ON;
IF OBJECT_ID('dbo.DatabaseMail_CreateAccount') IS NOT NULL
BEGIN
	/**************************************************/
	PRINT CHAR(9) + 'dropping';
	/**************************************************/
	DROP PROCEDURE dbo.DatabaseMail_CreateAccount; 36 END

	/**************************************************/
	PRINT CHAR(9) + 'creating';
	/**************************************************/
GO

CREATE PROCEDURE dbo.DatabaseMail_CreateAccount (
    @aSPMode                            INT                = 0
,    @aAccountName                      SYSNAME
,    @aEmailAddress                     NVARCHAR(128)
,    @aEmailDisplayName                 NVARCHAR(128)
,    @aEmailReplyToAddress              NVARCHAR(128)
,    @aAccountDescription               NVARCHAR(256)
,    @aMailServerName                   SYSNAME
,    @aMailServerPort                   INT                = 25
,    @aMailServerUserName               SYSNAME
,    @aMailServerPassword               SYSNAME
,    @aMailServerUseDefaultCredentials  BIT
,    @aMailServerEnableSSL              BIT
) AS
SET NOCOUNT ON;
SET QUOTED_IDENTIFIER ON;
/**************************************************
Declare default variables
**************************************************/
DECLARE
	@CalledSPReturnValue     INT                = 0
,    @CodeBlockDesc          NVARCHAR(256)    = ''
,    @ErrorID                INT                = 0
,    @ErrorDesc              NVARCHAR(256)    = 'No Error'
,    @ReturnValue            INT                = 0
,    @Rowcount               INT                = 0
,    @SPName                 NVARCHAR(128)    = 'dbo.DatabaseMail_CreateAccount'
;

/****************************
** Begin transaction
*****************************/
BEGIN TRAN

/**************************************************
Validate input parameters
**************************************************/
IF @aSPMode IS NULL
BEGIN
	SET @aSPMode = 0;
END

IF @aAccountName IS NULL
BEGIN
	SET @ReturnValue = -1;
	SET @ErrorDesc    = '@aAccountName input parameter is null';
	GOTO ErrorHandler;
END

IF @aEmailAddress IS NULL
BEGIN
	SET @ReturnValue = -1;
	SET @ErrorDesc    = '@aEmailAddress input parameter is null';
	GOTO ErrorHandler;
END

IF @aEmailDisplayName IS NULL
BEGIN
	SET @aEmailDisplayName = @aEmailAddress;
END

IF @aAccountDescription IS NULL
BEGIN
	SET @ReturnValue = -1;
	SET @ErrorDesc    = '@aAccountDescription input parameter is null';
	GOTO ErrorHandler;
END

IF @aMailServerName IS NULL
BEGIN
	SET @ReturnValue = -1;
	SET @ErrorDesc    = '@aMailServerName input parameter is null';
	GOTO ErrorHandler;
END

IF EXISTS(SELECT * FROM msdb.dbo.sysmail_account WHERE name = @aAccountName)
BEGIN
	/**************************************************/
	SET @CodeBlockDesc = 'Updating account'
	/**************************************************/
	BEGIN TRY
		EXEC @CalledSPReturnValue = msdb.dbo.sysmail_update_account_sp
			@account_name                = @aAccountName
		,    @email_address                = @aEmailAddress
		,    @display_name                = @aEmailDisplayName
		,    @replyto_address            = @aEmailReplyToAddress
		,    @description                = @aAccountDescription
		,    @mailserver_name            = @aMailServerName
		,    @port                        = @aMailServerPort
		,    @username                    = @aMailServerUserName
		,    @password                    = @aMailServerPassword
		,    @use_default_credentials    = @aMailServerUseDefaultCredentials
		,    @enable_ssl                    = @aMailServerEnableSSL
		;

		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
END
ELSE
BEGIN
	/**************************************************/
	SET @CodeBlockDesc = 'Adding account'
	/**************************************************/
	BEGIN TRY
		EXEC @CalledSPReturnValue = msdb.dbo.sysmail_add_account_sp
			@account_name                = @aAccountName
			,    @email_address                = @aEmailAddress
			,    @display_name                = @aEmailDisplayName
			,    @replyto_address            = @aEmailReplyToAddress
			,    @description                = @aAccountDescription
			,    @mailserver_name            = @aMailServerName
			,    @port                        = @aMailServerPort
			,    @username                    = @aMailServerUserName
			,    @password                    = @aMailServerPassword
			,    @use_default_credentials    = @aMailServerUseDefaultCredentials
			,    @enable_ssl                    = @aMailServerEnableSSL
			;

	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
END

/**************************************************/
CommitTransaction:
/**************************************************/
IF XACT_STATE() = 1
BEGIN
	COMMIT TRAN
END

RETURN @ReturnValue;

/**************************************************/
ErrorHandler:
/**************************************************/
IF XACT_STATE() = 1 AND @ReturnValue \>= 0
BEGIN
	COMMIT TRAN;
END

IF XACT_STATE() != 0
BEGIN
	ROLLBACK TRAN;
END

RETURN @ReturnValue;
GO

Next, how to create a profile that will be able to use one or more accounts - Database Mail, Part 3 - Creating a Profile

Posts in this Series


Comment on this post: