T-SQL inconsistent sp_send_dbmail results

Question

I have a job set up on a handful of servers (all in the same domain). The job sends an email via sp_send_dbmail, and the subject of the email should look like the following format:

=servername (ip address)= Weekly DB Backup to Azure Report

So as a potential example (obviously replace the 0s with actual IP address of the server SQL is running on):

=SQLMACHINE (000.000.000.00)= Weekly Backup to Azure Report

DBmail is configured, and I created the backup job. The T-SQL job step that sends the email has the following script:

SET NOCOUNT ON
DECLARE @ipAddress NVARCHAR(100) 

SELECT @ipAddress = local_net_address 
FROM sys.dm_exec_connections 
WHERE Session_id = @@SPID;

DECLARE @subjectText NVARCHAR(255) = N'=' + 
    CAST(LEFT(@@SERVERNAME, CHARINDEX('\', @@SERVERNAME)-1) AS NVARCHAR) + N'.' + 
    CAST(DEFAULT_DOMAIN() AS NVARCHAR) + N' ('+ @ipAddress + 
    N')= Weekly DB Backup to Azure Report'

DECLARE @tableHTML NVARCHAR(MAX) = N'this part works fine'

exec msdb.dbo.sp_send_dbmail @profile_name = 'Production Mail',
                             @recipients = 'xxx@xxx.com',
                             @subject = @subjectText,
                             @body = @tableHTML,
                             @body_format = 'HTML'

Each of the 5 servers has the same exact job definition - I have 1 source-controlled definition of the job that I use to create the job on each server.

Each week when the jobs run, most of them kick off an email with the expected subject line. Every couple weeks though, an email comes through with the subject SQL Server Message, which is what happens when no subject has been specified. Each time this happens, it could be on any one of the five servers. I'm not sure what's happening, since it should have a subject each time it executes.

EDIT: This is happening because the @ipAddress variable is null. No idea why

SELECT @ipAddress = local_net_address 
FROM sys.dm_exec_connections 
WHERE Session_id = @@SPID;

would return null though...

@STLDeveloper Where? Copying the above directly into a new query window in SSMS (and changing the email address to a valid one) works. — Jeffrey Van Laethem, Sep 12 '16 at 20:27
My apologies - you're correct. SO's quote tracking logic must have failed, causing the color syntax highlighting to be wrong. Looks like it broke after the first argument to the `CHARINDEX` function. I guess it doesn't like `'\'`. — STLDev, Sep 12 '16 at 20:30
Ohhh, I see what you're talking about. Yeah, at first glance it looks that way. :) — Jeffrey Van Laethem, Sep 12 '16 at 20:31
how are you generating the IP address? If the value comes back as NULL and then you concatenate it, your subject line will be NULL (for any calculated field). Is that possible? — paulbarbin, Sep 12 '16 at 20:37
The only thing I can think of is that your `@ipAddress` is somehow returning `NULL` when you are setting it (sometimes), which will make your `@subjectText = NULL`. I'm not sure why it'd ever return NULL... maybe it's a timing issue. Maybe try placing a `WAITFOR DELAY '00:00:02'` before you set the `@ipAddress` — S3S, Sep 12 '16 at 20:38
I agree with scsimon.......that the (at)ipAddress may be null. for the code + (at)ipAddress + ...... put in an isnull check , ex: + ISNULL(@ipAddress,'0.0.0.0') + — granadaCoder, Sep 12 '16 at 20:49
Looks like the null ipAddress is the issue... WAITFOR_DELAY was a good idea, but seems to still have the issue. I wrapped the ipAddress in an isnull() and the subject line is populating, albeit with the null substitution. I'll update my question, because it's now "Why would my connection's IP address be null sometimes?" — Jeffrey Van Laethem, Sep 12 '16 at 20:50
@scsimon That's true about azure, but the servers this is running on aren't Azure. The backup jobs just send the backups to Azure Blob storage, hence the name. — Jeffrey Van Laethem, Sep 12 '16 at 20:53
I assume you get an IP address when you run it locally, as you, but NULL when SQL Server Agent executes it? — S3S, Sep 12 '16 at 21:03

score 1 · Accepted Answer · answered Sep 12 '16 at 23:31

local_net_address will be always NULL if a connection is not using the TCP transport provider. It's likely you use 'Shared memory' in net_transport.

You can force to use TCP when you create a connection, so local_net_address will be populated. E.g. when you open SSMS, you can specify the server name as "tcp:ServerName\InstanceName"

Below also can be used to retrieve the server properties (using TCP transport):

SELECT  
   CONNECTIONPROPERTY('net_transport') AS net_transport,
   CONNECTIONPROPERTY('protocol_type') AS protocol_type,
   CONNECTIONPROPERTY('auth_scheme') AS auth_scheme,
   CONNECTIONPROPERTY('local_net_address') AS local_net_address,
   CONNECTIONPROPERTY('local_tcp_port') AS local_tcp_port,
   CONNECTIONPROPERTY('client_net_address') AS client_net_address

This is the issue! Just found the answer myself. Is there a way to force an agent job to use top instead of shared memory without altering the order in configuration manager? — Jeffrey Van Laethem, Sep 13 '16 at 12:45

score 0 · Answer 2 · answered Sep 13 '16 at 12:38

0

Your @ipAddress value could be null

Replace this code:

 + @ipAddress +

with this code

+ ISNULL(@ipAddress,'0.0.0.0') +

to prove that is the issue.

answered Sep 13 '16 at 12:38

granadaCoder

26,328
10
113
146

T-SQL inconsistent sp_send_dbmail results

2 Answers2