Post reply

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

  • 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?

  • 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!

  • 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?

  • 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!

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply