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 = '[email protected]',
@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...
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