다음을 통해 공유

When DBMail started complaining about the servername being NULL

  • 아티클

I recently came across an issue, where, for some reason, DBMail was not working. To be more specific, we were unable to create a profile for DBMail, let alone send emails. When trying to add the profile to the account, we were getting this error:

TITLE: Configuring...
------------------------------
Unable to create new account test for SMTP server Microsoft.SqlServer.Management.SqlManagerUI.SQLiMailServer.
------------------------------
ADDITIONAL INFORMATION:
Create failed for MailAccount 'test'.  (Microsoft.SqlServer.Smo)
For help, click: https://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.50.2500.0+((KJ_PCU_Main).110617-0038+)&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.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)
For help, click: https://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.50.2500&EvtSrc=MSSQLServer&EvtID=515&LinkId=20476
------------------------------
BUTTONS:
OK
------------------------------

Now, looking at the error message, it’s clear that we’re somehow passing NULL for the servername field when creating the profile. I tried creating a profile using T-SQL (using the steps mentioned here), and that worked just fine. I could also see the row in the msdb.dbo.sysmail_server table.

So there was definitely an issue with how the servername value was being captured/passed. I captured a profiler trace, and found the following rows to be of interest:

SP:StmtCompleted        SELECT @mailserver_name=@@SERVERNAME   
   --create a credential in the credential store if a password needs to be stored   
       Microsoft SQL Server Management Studio

SP:StmtStarting             IF(@username IS NOT NULL)
       Microsoft SQL Server Management Studio

SP:StmtCompleted       IF(@username IS NOT NULL)
       Microsoft SQL Server Management Studio

SP:StmtStarting           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)   
       Microsoft SQL Server Management Studio

Exception                     Error: 515, Severity: 16, State: 2
User Error Message     Cannot insert the value NULL into column 'servername', table 'msdb.dbo.sysmail_server'; column does not allow nulls. INSERT fails.
User Error Message     The statement has been terminated.

Accordingly, I tried running select @@SERVERNAME explicitly on the server, and lo and behold, that was NULL too…!!! However, the select serverproperty(‘servername’) command was able to return the server name. But unfortunately, DBMail uses select @@SERVERNAME, and not serverproperty (‘servername’), as we can see clearly in the profiler trace. So this was definitely where the issue was originating from. I then queried the sys.sysservers dmv, and I couldn’t see a record with the srvid 0 (the details of the local server are always stored in the dmv with srvid 0). Next, we ran the following commands to fix the situation:

sp_dropserver ‘<localservername>’

sp_addserver ‘<localservername>’, @local=’LOCAL’

After this, we restarted SQL Server, and DBMail worked like a charm (after we had cleaned up the mess we had created earlier, of course). Hope this helps.

Comments

  • Anonymous
    June 10, 2014
    right solution.