Database Mail, Part 3 - Creating a Profile
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
Comment on this post: