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.

1 /************************************************** 2 Name: dbo.DatabaseMail_CreateProfile 3 Written by: Al Eardley 4 Date: December 2010 5 Purpose: 6 This script creates a stored procedure that creates a Database Mail Profile 7 8 Parameters: 9 @aSPMode - Not currently used 10 11 Execution: 12 DECLARE @rv int 13 EXEC @rv = dbo.DatabaseMail_CreateProfile 14 @aSPMode = 0 15 , @aAccountName = 'TestAccount' 16 , @aProfileName = 'TestProfile' 17 , @aProfileDesc = 'Test Profile Description' 18 , @aRelationshipSequence = 1 19 PRINT @rv 20 21 **************************************************/ 22 23 /************************************************** 24 ** Initialise Script 25 **************************************************/ 26 PRINT 'dbo.DatabaseMail_CreateProfile'; 27 28 SET ANSI_NULLS ON; 29 SET NOCOUNT ON; 30 31 /************************************************** 32 ** Delete SP 33 **************************************************/ 34 IF OBJECT_ID('dbo.DatabaseMail_CreateProfile') IS NOT NULL 35 BEGIN 36 PRINT CHAR(9) + 'dropping'; 37 DROP PROCEDURE dbo.DatabaseMail_CreateProfile; 38 END 39 GO 40 41 /************************************************** 42 ** Create SP 43 **************************************************/ 44 CREATE PROCEDURE dbo.DatabaseMail_CreateProfile ( 45 @aSPMode INT = 0 46 , @aAccountName SYSNAME 47 , @aProfileName SYSNAME 48 , @aProfileDesc NVARCHAR(256) 49 , @aRelationshipSequence INT = 1 50 ) AS 51 52 SET NOCOUNT ON; 53 SET QUOTED_IDENTIFIER ON; 54 55 /************************************************** 56 Declare default variables 57 **************************************************/ 58 DECLARE 59 @CalledSPReturnValue INT = 0 60 , @CodeBlockDesc NVARCHAR(256) = '' 61 , @ErrorID INT = 0 62 , @ErrorDesc NVARCHAR(256) = 'No Error' 63 , @ReturnValue INT = 0 64 , @Rowcount INT = 0 65 , @SPName NVARCHAR(128) = 'dbo.DatabaseMail_CreateProfile' 66 ; 67 68 /************************************************** 69 Declare local variables 70 **************************************************/ 71 DECLARE 72 @AccountID INT 73 , @ProfileID INT 74 ; 75 76 /**************************** 77 ** Begin transaction 78 *****************************/ 79 BEGIN TRAN 80 BEGIN TRY 81 /**************************************************/ 82 SET @CodeBlockDesc = 'Get account ID' 83 /**************************************************/ 84 SELECT 85 @AccountID = SMA.account_id 86 FROM msdb.dbo.sysmail_account SMA 87 WHERE 88 SMA.name = @aAccountName 89 ; 90 91 IF @@rowcount = 0 92 BEGIN 93 SET @ErrorDesc = 'No rows affected: ' + @CodeBlockDesc; 94 SET @ReturnValue = 1; 95 GOTO ErrorHandler; 96 END 97 END TRY 98 BEGIN CATCH 99 SET @ErrorDesc = 'Fatal Error: ' + @CodeBlockDesc + ': ' + CAST(ERROR_LINE() AS nvarchar) + ' - ' + CAST(ERROR_NUMBER() AS nvarchar) + ' - ' + ERROR_MESSAGE(); 100 SET @ReturnValue = -1; 101 GOTO ErrorHandler; 102 END CATCH 103 104 IF EXISTS (SELECT * FROM msdb.dbo.sysmail_profile SMP WHERE SMP.name = @aProfileName) 105 BEGIN 106 BEGIN TRY 107 /**************************************************/ 108 SET @CodeBlockDesc = 'Get profile ID' 109 /**************************************************/ 110 SELECT 111 @ProfileID = SMP.profile_id 112 FROM msdb.dbo.sysmail_profile SMP 113 WHERE 114 SMP.name = @aProfileName 115 ; 116 117 IF @@rowcount = 0 118 BEGIN 119 SET @ErrorDesc = 'No rows affected: ' + @CodeBlockDesc; 120 SET @ReturnValue = 1; 121 GOTO ErrorHandler; 122 END 123 END TRY 124 BEGIN CATCH 125 SET @ErrorDesc = 'Fatal Error: ' + @CodeBlockDesc + ': ' + CAST(ERROR_LINE() AS nvarchar) + ' - ' + CAST(ERROR_NUMBER() AS nvarchar) + ' - ' + ERROR_MESSAGE(); 126 SET @ReturnValue = -1; 127 GOTO ErrorHandler; 128 END CATCH 129 130 BEGIN TRY 131 /**************************************************/ 132 SET @CodeBlockDesc = 'Update profile' 133 /**************************************************/ 134 EXEC @CalledSPReturnValue = msdb.dbo.sysmail_update_profile_sp 135 @profile_id = @ProfileID 136 , @profile_name = @aProfileName 137 , @description = @aProfileDesc 138 ; 139 140 IF @CalledSPReturnValue != 0 141 BEGIN 142 SET @ErrorDesc = 'Hard Error: ' + @CodeBlockDesc + ': RV=' + CAST(@CalledSPReturnValue AS varchar); 143 SET @ReturnValue = @CalledSPReturnValue; 144 GOTO ErrorHandler; 145 END 146 END TRY 147 BEGIN CATCH 148 SET @ErrorDesc = 'Fatal Error: ' + @CodeBlockDesc + ': ' + CAST(ERROR_LINE() AS nvarchar) + ' - ' + CAST(ERROR_NUMBER() AS nvarchar) + ' - ' + ERROR_MESSAGE(); 149 SET @ReturnValue = -1; 150 GOTO ErrorHandler; 151 END CATCH 152 END 153 ELSE 154 BEGIN 155 BEGIN TRY 156 /**************************************************/ 157 SET @CodeBlockDesc = 'Add profile' 158 /**************************************************/ 159 EXEC @CalledSPReturnValue = msdb.dbo.sysmail_add_profile_sp 160 @profile_name = @aProfileName 161 , @description = @aProfileDesc 162 , @profile_id = @ProfileID OUTPUT 163 ; 164 165 IF @CalledSPReturnValue != 0 166 BEGIN 167 SET @ErrorDesc = 'Hard Error: ' + @CodeBlockDesc + ': RV=' + CAST(@CalledSPReturnValue AS varchar); 168 SET @ReturnValue = @CalledSPReturnValue; 169 GOTO ErrorHandler; 170 END 171 END TRY 172 BEGIN CATCH 173 SET @ErrorDesc = 'Fatal Error: ' + @CodeBlockDesc + ': ' + CAST(ERROR_LINE() AS nvarchar) + ' - ' + CAST(ERROR_NUMBER() AS nvarchar) + ' - ' + ERROR_MESSAGE(); 174 SET @ReturnValue = -1; 175 GOTO ErrorHandler; 176 END CATCH 177 END 178 179 IF EXISTS (SELECT * FROM msdb.dbo.sysmail_profileaccount SMPA WHERE SMPA.profile_id = @ProfileID AND SMPA.account_id = @AccountID) 180 BEGIN 181 BEGIN TRY 182 /**************************************************/ 183 SET @CodeBlockDesc = 'Update profile/account relationship' 184 /**************************************************/ 185 EXEC @CalledSPReturnValue = msdb.dbo.sysmail_update_profileaccount_sp 186 @profile_id = @ProfileID 187 , @account_id = @AccountID 188 , @sequence_number = @aRelationshipSequence 189 ; 190 191 IF @CalledSPReturnValue != 0 192 BEGIN 193 SET @ErrorDesc = 'Hard Error: ' + @CodeBlockDesc + ': RV=' + CAST(@CalledSPReturnValue AS varchar); 194 SET @ReturnValue = @CalledSPReturnValue; 195 GOTO ErrorHandler; 196 END 197 END TRY 198 BEGIN CATCH 199 SET @ErrorDesc = 'Fatal Error: ' + @CodeBlockDesc + ': ' + CAST(ERROR_LINE() AS nvarchar) + ' - ' + CAST(ERROR_NUMBER() AS nvarchar) + ' - ' + ERROR_MESSAGE(); 200 SET @ReturnValue = -1; 201 GOTO ErrorHandler; 202 END CATCH 203 END 204 ELSE 205 BEGIN 206 BEGIN TRY 207 /**************************************************/ 208 SET @CodeBlockDesc = 'Add profile/account relationship' 209 /**************************************************/ 210 EXEC @ReturnValue = msdb.dbo.sysmail_add_profileaccount_sp 211 @profile_id = @ProfileID 212 , @account_id = @AccountID 213 , @sequence_number = @aRelationshipSequence 214 ; 215 216 IF @CalledSPReturnValue != 0 217 BEGIN 218 SET @ErrorDesc = 'Hard Error: ' + @CodeBlockDesc + ': RV=' + CAST(@CalledSPReturnValue AS varchar); 219 SET @ReturnValue = @CalledSPReturnValue; 220 GOTO ErrorHandler; 221 END 222 END TRY 223 BEGIN CATCH 224 SET @ErrorDesc = 'Fatal Error: ' + @CodeBlockDesc + ': ' + CAST(ERROR_LINE() AS nvarchar) + ' - ' + CAST(ERROR_NUMBER() AS nvarchar) + ' - ' + ERROR_MESSAGE(); 225 SET @ReturnValue = -1; 226 GOTO ErrorHandler; 227 END CATCH 228 END 229 230 231 /**************************************************/ 232 CommitTransaction: 233 /**************************************************/ 234 IF XACT_STATE() = 1 235 BEGIN 236 COMMIT TRAN 237 END 238 239 RETURN @ReturnValue; 240 241 /**************************************************/ 242 ErrorHandler: 243 /**************************************************/ 244 IF XACT_STATE() = 1 AND @ReturnValue >= 0 245 BEGIN 246 COMMIT TRAN; 247 END 248 249 IF XACT_STATE() != 0 250 BEGIN 251 ROLLBACK TRAN; 252 END 253 254 RETURN @ReturnValue; 255 256 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.

Leave a Reply

Your email address will not be published.