SQL Server: DB Mail Account Creation Failed - A Bug

One fine day i was configuring DB Mail on one of my server and while configuring Mail Account i encountered the following error.

This Bug\Problem is applicable to SQLServer 2008 SP1, I've faced it on Standard Edition 64-Bit.

Error:  Unable to create new account SQLServerGeeks for SMTP server Microsoft.SqlServer.Management. SqlManagerUI. SQLiMailServer.

ADDITIONAL INFORMATION:
Create failed for MailAccount 'SQLServerGeeks'.  (Microsoft.SqlServer.Smo)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer = 10.0.2531.0+((Katmai_PCU_Main).090329-1045+)&EvtSrc=Microsoft.SqlServer.Management. Smo.Exception Templates.FailedOperationExceptionText&EvtID=Create+MailAccount&LinkId=20476 

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
Cannot insert the value NULL into column 'servername', table 'msdb.dbo.sysmail_server'; column does not allow nulls.
INSERT fails.
The statement has been terminated. (Microsoft SQL Server, Error: 515)

As per the error, Server Name field was left blank while configuring the account and since the Column in the table doesn't allow Nulls it was failing.

I tried it once again and this time double checked the value provided in server name, (checked both ways providing Server IP | Hostname of the relay server) but it landed up again on the same error message.

But this time i choose to look into the Advance info Page to get more info about the error, and Woopie i got the name of the SP which was running behind the scene by Wizard.

I was sure there’s something wrong with this Stored Procedure.

After reading this error :-->
Server Name:
Error Number: 515
Severity: 16
State: 2
Procedure: sysmail_add_account_sp
Line Number: 61

So i started looking into the Code of this SP. While analyzing the SP, i found that the SP itself was missing the Server_Name in the first Insert Statement which was the Real Root Cause for the error.

Please find below the full code of the faulty SP along with the version\Edition of SQL Server i was running.

I was about to change the SP but left it to see if the Script to create the Database Mail account was working fine or that also has some problems, But the script worked like Anything.

Solutions

1.     Use T-SQL Code to create a DB Mail Account.
2.     Upgrade your SQL Server Instance to Latest Service Pack.
3.     Update the Code mentioned below in Stored Procedure-sysmail_add_account_sp.

Later i found that this is a Bug and many people have faced it.

Script to Create the DB mail Account:

/*EXECUTE msdb.dbo.sysmail_add_account_sp 
@account_name = 'SQL Server Admin',
@description = 'SQL Server Notification Service',
@email_address = ‘sarab@SQLServerGeeks.com',
@replyto_address = ‘sarab@SQLServerGeeks’ ',
@display_name = ‘Sarabpreet',
@mailserver_name = 'smtpserver.SQLServerGeeks.com';*/

Root Cause of the problem --(Faulty Code in SP)

INSERT INTO msdb.dbo.sysmail_account (name,description,email_address,display_name,replyto_address)
VALUES (@account_name,@description,@email_address,@display_name,@replyto_address)

Full Script of the Faulty Stored procedure:

/*
CREATE PROCEDURE dbo.sysmail_add_account_sp
@account_name sysname,
@email_address nvarchar(128),
@display_name nvarchar(128) = NULL,
@replyto_address nvarchar(128) = NULL,
@description nvarchar(256) = NULL,
@mailserver_name sysname = NULL, -- the following fields are part of server definition
@mailserver_type sysname = N'SMTP',
@port int = 25,
@username nvarchar(128) = NULL,
@password nvarchar(128) = NULL,
@use_default_credentials bit = 0,
@enable_ssl bit = 0,
@account_id int = NULL OUTPUT
AS
SET NOCOUNT ON
DECLARE @rc int
DECLARE @credential_id int
EXEC @rc = msdb.dbo.sysmail_verify_accountparams_sp
@use_default_credentials = @use_default_credentials,
@mailserver_type = @mailserver_type OUTPUT, -- validates and returns trimmed value
@username = @username OUTPUT, -- returns trimmed value, NULL if empty
@password = @password OUTPUT  -- returns trimmed value, NULL if empty
IF(@rc <> 0)
RETURN (1)
--transact this in case sysmail_create_user_credential_sp fails
BEGIN TRANSACTION
-- insert new account record, rely on primary key constraint to error out
INSERT INTO msdb.dbo.sysmail_account (name,description,email_address,display_name,replyto_address)
VALUES (@account_name,@description,@email_address,@display_name,@replyto_address)
IF (@@ERROR <> 0)
BEGIN
ROLLBACK TRANSACTION
RETURN (2)
END
-- fetch back account_id
SELECT @account_id = account_id FROM msdb.dbo.sysmail_account WHERE name = @account_name
IF (@mailserver_name IS NULL) -- use local server as default
SELECT @mailserver_name=@@SERVERNAME
--create a credential in the credential store if a password needs to be stored
IF(@username IS NOT NULL)
BEGIN
EXEC @rc = msdb.dbo.sysmail_create_user_credential_sp
@username,
@password,
@credential_id OUTPUT
IF(@rc <> 0)
BEGIN
ROLLBACK TRANSACTION
RETURN (3)
END
END
INSERT INTO msdb.dbo.sysmail_server
(account_id,servertype,servername,port,username,credential_id,use_default_credentials,enable_ssl)
VALUES
(@account_id,@mailserver_type,@mailserver_name,@port,@username,@credential_id,
@use_default_credentials,@enable_ssl)
IF (@@ERROR <> 0)
BEGIN
ROLLBACK TRANSACTION
RETURN (4)
END
COMMIT TRANSACTION
RETURN(0)
*/

eNjoy

Thanks,

Sarabpreet Singh