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)

1_SQL_Server_DB_Mail_Account_Creation_Failed_A_Bug

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.

2_SQL_Server_DB_Mail_Account_Creation_Failed_A_Bug

3_SQL_Server_DB_Mail_Account_Creation_Failed_A_Bug

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.

4_SQL_Server_DB_Mail_Account_Creation_Failed_A_Bug

5_SQL_Server_DB_Mail_Account_Creation_Failed_A_Bug

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:

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

Full Script of the Faulty Stored procedure:

eNjoy

 

Regards

Sarabpreet Anand

Like us on FaceBook Follow us on Twitter | Join the fastest growing SQL Server group on FaceBook

Follow me on Twitter  |  Follow me on FaceBook