CONNECTIONPROPERTY('local_net_address') does not work in SQL Scheduled job.

• jim Etheridge

  SSC-Addicted

  Points: 430

  More actions

  June 15, 2015 at 6:48 am

  #305120

  CONNECTIONPROPERTY('local_net_address') returns NULL when I run it in a SQL Scheduled job. The job is executing as 'NT SERVICE\SQLSERVERAGENT'. Running it in a MS query as 'NT SERVICE\SQLSERVERAGENT' works.

  Any ideas?

• Lowell

  SSC Guru

  Points: 323518

  More actions

  June 15, 2015 at 7:18 am

  #1805443

  wouldn't NULL be normal if the connection was over named pipes, and not TCP/IP?

  i'd think that that is probably what you are seeing.

  can you check within your script?

  SELECT net_transport

  FROM sys.dm_exec_connections

  WHERE session_id = @@SPID;

  Lowell

  ---

  --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

• jim Etheridge

  SSC-Addicted

  Points: 430

  More actions

  June 15, 2015 at 10:02 am

  #1805506

  When I run your query in MS I get 'TCP'.

  When I run it in the SQL job I get 'Shared memory'.

  What does that mean, exactly?

• Lowell

  SSC Guru

  Points: 323518

  More actions

  June 15, 2015 at 10:13 am

  #1805512

  there's multiple ways to connect ot SQL; you can see that whenever you look at the SQL configuration manager.

  

  as i understand it, shared memory is always loca, since it's local memory;

  named pipes would typically be the next fastest, and is typical for a local connections, but can be foreced to other servers ...I'd actually expect to see Named Pipes on a job or SSMS connection connecting to the same server it's hosted on.

  TCP/IP has to go throught he network and n9ic cqard, and might use a dns server to find the target servers before anythimg goes on; network traffic can slow things down(potentially), and it's typically a bit slower.

  so the issue you are seeing is "what IP are you using", but sometimes the connection does not use TCP/IP, so the data is null.

  i'd just substitute (localhost) when null for reporting/logging purposes.

  Lowell

  ---

  --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!