Database Mail, Part 2–Creating an Account

In Database Mail, Part 1 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.

1 /************************************************** 2 Name: dbo.DatabaseMail_CreateAccount 3 Written by: Al Eardley 4 Date: December 2010 5 Purpose: 6 This script creates a stored procedure that creates a Database Mail Account 7 8 Parameters: 9 @aSPMode - Not currently used 10 11 Execution: 12 DECLARE @rv int 13 EXEC @rv = dbo.DatabaseMail_CreateAccount 14 @aSPMode = 0 15 , @aAccountName = 'TestAccount' 16 , @aAccountDescription = 'Test Account Description' 17 , @aEmailAddress = 'Administrator@localhost' 18 , @aMailServerName = 'localhost' 19 PRINT @rv 20 21 **************************************************/ 22 23 /**************************************************/ 24 PRINT 'dbo.DatabaseMail_CreateAccount'; 25 /**************************************************/ 26 27 SET ANSI_NULLS ON; 28 SET NOCOUNT ON; 29 30 IF OBJECT_ID('dbo.DatabaseMail_CreateAccount') IS NOT NULL 31 BEGIN 32 /**************************************************/ 33 PRINT CHAR(9) + 'dropping'; 34 /**************************************************/ 35 DROP PROCEDURE dbo.DatabaseMail_CreateAccount; 36 END 37 38 /**************************************************/ 39 PRINT CHAR(9) + 'creating'; 40 /**************************************************/ 41 GO 42 43 CREATE PROCEDURE dbo.DatabaseMail_CreateAccount ( 44 @aSPMode INT = 0 45 , @aAccountName SYSNAME 46 , @aEmailAddress NVARCHAR(128) 47 , @aEmailDisplayName NVARCHAR(128) 48 , @aEmailReplyToAddress NVARCHAR(128) 49 , @aAccountDescription NVARCHAR(256) 50 , @aMailServerName SYSNAME 51 , @aMailServerPort INT = 25 52 , @aMailServerUserName SYSNAME 53 , @aMailServerPassword SYSNAME 54 , @aMailServerUseDefaultCredentials BIT 55 , @aMailServerEnableSSL BIT 56 ) AS 57 58 SET NOCOUNT ON; 59 SET QUOTED_IDENTIFIER ON; 60 61 /************************************************** 62 Declare default variables 63 **************************************************/ 64 DECLARE 65 @CalledSPReturnValue INT = 0 66 , @CodeBlockDesc NVARCHAR(256) = '' 67 , @ErrorID INT = 0 68 , @ErrorDesc NVARCHAR(256) = 'No Error' 69 , @ReturnValue INT = 0 70 , @Rowcount INT = 0 71 , @SPName NVARCHAR(128) = 'dbo.DatabaseMail_CreateAccount' 72 ; 73 74 /************************************************** 75 Declare local variables 76 **************************************************/ 77 78 /**************************** 79 ** Begin transaction 80 *****************************/ 81 BEGIN TRAN 82 83 /************************************************** 84 ** Validate input parameters 85 **************************************************/ 86 IF @aSPMode IS NULL 87 BEGIN 88 SET @aSPMode = 0; 89 END 90 91 IF @aAccountName IS NULL 92 BEGIN 93 SET @ReturnValue = -1; 94 SET @ErrorDesc = '@aAccountName input parameter is null'; 95 GOTO ErrorHandler; 96 END 97 98 IF @aEmailAddress IS NULL 99 BEGIN 100 SET @ReturnValue = -1; 101 SET @ErrorDesc = '@aEmailAddress input parameter is null'; 102 GOTO ErrorHandler; 103 END 104 105 IF @aEmailDisplayName IS NULL 106 BEGIN 107 SET @aEmailDisplayName = @aEmailAddress; 108 END 109 110 IF @aAccountDescription IS NULL 111 BEGIN 112 SET @ReturnValue = -1; 113 SET @ErrorDesc = '@aAccountDescription input parameter is null'; 114 GOTO ErrorHandler; 115 END 116 117 IF @aMailServerName IS NULL 118 BEGIN 119 SET @ReturnValue = -1; 120 SET @ErrorDesc = '@aMailServerName input parameter is null'; 121 GOTO ErrorHandler; 122 END 123 124 IF EXISTS(SELECT * FROM msdb.dbo.sysmail_account WHERE name = @aAccountName) 125 BEGIN 126 /**************************************************/ 127 SET @CodeBlockDesc = 'Updating account' 128 /**************************************************/ 129 BEGIN TRY 130 EXEC @CalledSPReturnValue = msdb.dbo.sysmail_update_account_sp 131 @account_name = @aAccountName 132 , @email_address = @aEmailAddress 133 , @display_name = @aEmailDisplayName 134 , @replyto_address = @aEmailReplyToAddress 135 , @description = @aAccountDescription 136 , @mailserver_name = @aMailServerName 137 , @port = @aMailServerPort 138 , @username = @aMailServerUserName 139 , @password = @aMailServerPassword 140 , @use_default_credentials = @aMailServerUseDefaultCredentials 141 , @enable_ssl = @aMailServerEnableSSL 142 ; 143 144 IF @CalledSPReturnValue != 0 145 BEGIN 146 SET @ErrorDesc = 'Hard Error: ' + @CodeBlockDesc + ': RV=' + CAST(@CalledSPReturnValue AS varchar); 147 SET @ReturnValue = @CalledSPReturnValue; 148 GOTO ErrorHandler; 149 END 150 END TRY 151 BEGIN CATCH 152 SET @ErrorDesc = 'Fatal Error: ' + @CodeBlockDesc + ': ' + CAST(ERROR_LINE() AS nvarchar) + ' - ' + CAST(ERROR_NUMBER() AS nvarchar) + ' - ' + ERROR_MESSAGE(); 153 SET @ReturnValue = -1; 154 GOTO ErrorHandler; 155 END CATCH 156 END 157 ELSE 158 BEGIN 159 /**************************************************/ 160 SET @CodeBlockDesc = 'Adding account' 161 /**************************************************/ 162 BEGIN TRY 163 EXEC @CalledSPReturnValue = msdb.dbo.sysmail_add_account_sp 164 @account_name = @aAccountName 165 , @email_address = @aEmailAddress 166 , @display_name = @aEmailDisplayName 167 , @replyto_address = @aEmailReplyToAddress 168 , @description = @aAccountDescription 169 , @mailserver_name = @aMailServerName 170 , @port = @aMailServerPort 171 , @username = @aMailServerUserName 172 , @password = @aMailServerPassword 173 , @use_default_credentials = @aMailServerUseDefaultCredentials 174 , @enable_ssl = @aMailServerEnableSSL 175 ; 176 177 IF @CalledSPReturnValue != 0 178 BEGIN 179 SET @ErrorDesc = 'Hard Error: ' + @CodeBlockDesc + ': RV=' + CAST(@CalledSPReturnValue AS varchar); 180 SET @ReturnValue = @CalledSPReturnValue; 181 GOTO ErrorHandler; 182 END 183 END TRY 184 BEGIN CATCH 185 SET @ErrorDesc = 'Fatal Error: ' + @CodeBlockDesc + ': ' + CAST(ERROR_LINE() AS nvarchar) + ' - ' + CAST(ERROR_NUMBER() AS nvarchar) + ' - ' + ERROR_MESSAGE(); 186 SET @ReturnValue = -1; 187 GOTO ErrorHandler; 188 END CATCH 189 END 190 191 /**************************************************/ 192 CommitTransaction: 193 /**************************************************/ 194 IF XACT_STATE() = 1 195 BEGIN 196 COMMIT TRAN 197 END 198 199 RETURN @ReturnValue; 200 201 /**************************************************/ 202 ErrorHandler: 203 /**************************************************/ 204 IF XACT_STATE() = 1 AND @ReturnValue >= 0 205 BEGIN 206 COMMIT TRAN; 207 END 208 209 IF XACT_STATE() != 0 210 BEGIN 211 ROLLBACK TRAN; 212 END 213 214 RETURN @ReturnValue; 215 216 GO

Next, how to create a profile that will be able to use one or more accounts.

Leave a Reply

Your email address will not be published.