CONNECTIONPROPERTY('local_net_address') does not work in SQL Scheduled job.
-
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?
-
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! -
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?
-
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