Database Mail, Part 2 - Creating an Account
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
Comment on this post: