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.
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.
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 :–>
Error Number: 515
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.
- Use T-SQL Code to create a DB Mail Account.
- Upgrade your SQL Server Instance to Latest Service Pack.
- 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)
<em><strong>INSERT INTO msdb.dbo.sysmail_account (name,description,email_address,display_name,replyto_address) VALUES (@account_name,@description,@email_address,@display_name,@replyto_address)</strong></em>
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) */