Which Oracle client is SQL Server using for my linked server?

I'm far from a SQL Server Expert but try the sys.servers view:
https://msdn.microsoft.com/en-us/library/ms178530.aspx

Hopefully it has the PATH to the DLL it is using.

Just run a

sp_linkedservers

in your SQL and you will get a list of - see details from here:
https://msdn.microsoft.com/en-us/library/ms189519.aspx


To get a detailed report of the Linked Servers run the query below:

SELECT *
FROM sys.Servers a
LEFT OUTER JOIN sys.linked_logins b ON b.server_id = a.server_id
LEFT OUTER JOIN sys.server_principals c ON c.principal_id = b.local_principal_id


You could also go in SQL SSMS and script the Linked server by navigate to the place it is in SSMS and right click on it then look for

@server specifies the server name that will be used in the SQL statements.

@datasrc is an alias from tnsnames.ora file (%ORACLE_NOME%\network\admin):

Unfortunately, none of these options give me the detail I'm looking for - they all show me the name of my linked server and that it's using the "OraOLEDB.Oracle" provider, but they just refer to the server itself by the TNS name I've specified. I'm looking for the location of the actual DLL associated with that provider - something I can't seem to find in the configuration or even if I script the server, since you don't specify the DLL but instead specify just the provider name (and the provider is added automatically into SQL Server somehow when you install the driver).

My problem is that I've changed the location of a server and updated all the TNSNAMES.ORA files I can find on the server, but SQL Server is still trying to connect to the old location - there must be a TNSNAMES or some other connection I've missed somewhere. By finding the location of the connection file it's using (or the installation folder of the active provider), I'm hopeful I can nail down this configuration file and correct it.

Also Oracle provides a utility called tnsping and you can try use that one to "ping" each ORACLE linked server and it will return the location of files that particular connection is using

C:\> tnsping some_connection_name_or_IP


The utility should be in the Oracle client bin directory

BTW, MSSQL is caching connection data for some time. Restarting MSSQL service might be required to make sure it gets all changes.

It is possible that the SQL server uses the latest version installed when the linked servers is created. Try to add a second linked server now to the same Oracle database and see if the behavior is the same. Maybe you can describe the symptom that bothers you.

Are you sure you are using the tnsnames.ora files to get the Oracle configuration?

There are other possibilities.  They are typically found in the sqlnet.ora file.  Look at the NAMES.DIRECTORY_PATH variable.

http://docs.oracle.com/cd/E11882_01/network.112/e10835/sqlnet.htm#NETRF192

>> it was correct, but the SQLNET.ORA config file in that folder had the old IP address and was causing problems.

I'm curious:  What parameter in the sqlnet.ora file held the ip address?

Ryan, you don't have to close or to ask for closing a question if you grant points. Just grant the points as you wish and that will be it.

There is no single known parameter of sqlnet.ora holding an IP address on a client (there are white and black lists for a server, though), so we would be more than just interested in what that parameter should be.

No other answer provided the detail necessary to resolve my issue, but they contributed to the research and confirmation I needed. In my accepted solution, I provided walk-through to obtaining the detail that resolved my problem.