Database Mail, Part 3 - Creating a Profile

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

In Part 1 of this series I produced a stored procedure to enable Database Mail and in Part 2 I produced a stored procedure to create an account. Now, to be able to use an account, and to send an e-mail you need to have a profile.

A profile is related to one or more accounts and is the means by which you identify how the e-mail will be sent. The procedure that I have written will create a profile if one does not exist and will associate an account with it based on the @aRelationshipSequence.

What this means is that the primary account to be used to send e-mails from the profile can be associated with the profile if the value of @aRelationshipSequence is set to one. If for any reason the profile is not available then a secondary profile can be added by setting the the value of @aRelationshipSequence to 2.

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

Parameters:
    @aSPMode - Not currently used

Execution:
DECLARE @rv int
EXEC @rv = dbo.DatabaseMail_CreateProfile
     @aSPMode            	   = 0
,    @aAccountName             = 'TestAccount'
,    @aProfileName             = 'TestProfile'
,    @aProfileDesc             = 'Test Profile Description'
,    @aRelationshipSequence    = 1

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

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

SET ANSI_NULLS ON;
SET NOCOUNT ON;

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

/**************************************************
** Create SP
**************************************************/
CREATE PROCEDURE dbo.DatabaseMail_CreateProfile (
     @aSPMode                INT                = 0
,    @aAccountName            SYSNAME
,    @aProfileName            SYSNAME
,    @aProfileDesc            NVARCHAR(256)
,    @aRelationshipSequence    INT                = 1
) 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_CreateProfile'
;

/**************************************************
Declare local variables
**************************************************/
DECLARE 
	 @AccountID            INT
,    @ProfileID            INT
;

/****************************
** Begin transaction
*****************************/
BEGIN TRAN
BEGIN TRY
	/**************************************************/
	SET @CodeBlockDesc = 'Get account ID'
	/**************************************************/
	SELECT
		@AccountID = SMA.account_id
	FROM msdb.dbo.sysmail_account SMA
	WHERE
		SMA.name = @aAccountName
	;

	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

IF EXISTS (SELECT * FROM msdb.dbo.sysmail_profile SMP WHERE SMP.name = @aProfileName)
BEGIN
	BEGIN TRY
		/**************************************************/
		SET @CodeBlockDesc = 'Get profile ID'
		/**************************************************/
		SELECT
			@ProfileID = SMP.profile_id
		FROM msdb.dbo.sysmail_profile SMP
		WHERE
			SMP.name = @aProfileName
		;

		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

	BEGIN TRY
		/**************************************************/
		SET @CodeBlockDesc = 'Update profile'
		/**************************************************/
		EXEC @CalledSPReturnValue = msdb.dbo.sysmail_update_profile_sp
			 @profile_id     = @ProfileID
		,    @profile_name   = @aProfileName
		,    @description    = @aProfileDesc
		;

		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
	BEGIN TRY
		/**************************************************/
		SET @CodeBlockDesc = 'Add profile'
		/**************************************************/
		EXEC @CalledSPReturnValue = msdb.dbo.sysmail_add_profile_sp
			 @profile_name = @aProfileName
		,    @description = @aProfileDesc
		,    @profile_id = @ProfileID OUTPUT
		;

		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

IF EXISTS (SELECT * FROM msdb.dbo.sysmail_profileaccount SMPA WHERE SMPA.profile_id = @ProfileID AND SMPA.account_id = @AccountID)
BEGIN
	BEGIN TRY
		/**************************************************/
		SET @CodeBlockDesc = 'Update profile/account relationship'
		/**************************************************/
		EXEC @CalledSPReturnValue = msdb.dbo.sysmail_update_profileaccount_sp
			 @profile_id            = @ProfileID
		,    @account_id            = @AccountID
		,    @sequence_number    = @aRelationshipSequence
		;

		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
	BEGIN TRY
		/**************************************************/
		SET @CodeBlockDesc = 'Add profile/account relationship'
		/**************************************************/
		EXEC @ReturnValue = msdb.dbo.sysmail_add_profileaccount_sp
			@profile_id            = @ProfileID
		,    @account_id            = @AccountID
		,    @sequence_number    = @aRelationshipSequence
		;

		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

In the next part of the series I will explain how to track the status of the e-mails that have, or have not been sent - Database Mail, Part 4 - Sending an e-mail

Posts in this Series


Comment on this post: