As we all know that to connect from SQL Server to Oracle we need to Install Oracle Client at SQL End. But since I am stuck in one issue which has made me wondered about how things work behind the scenes in case of Linked Server. I have few questions and wanted clarification
Scenario: we have SQL Server 2008 64 bit running on machine. Also we have 32 and 64 bit Oracle client installed. We have a SQL Job under which a DTS package is running using 32-bit environment. Inside package, SQL is connecting to Oracle. Our job is failing. My questions are:
-
So when I run the job and it is connecting Oracle Server, will it use Linked Server or will directly use Oracle 32-bit client?
-
If linked server is failing will that can be the reason for job failure. Error message when Testing Linked Server is:
ORA-12154: TNS: could not resolve the connect identifier specified -
Is the order in which oracle bin path is defined in PATH System Environment variable can impact this issue in anyway?
-
After making the changes in PATH System Environment variable do we need to restart SQL Server service or entire server?
I can provide more inputs for more clarifications. Any help is appreciated.
Best Answer
Depends on how the DTS package is written, since DTS was the old 2000 era packages I have no idea if they imported a connection in the package or query through the linked servers. You'd have to investigate to see if it references the linked server using TSQL or it uses a connection, directly.
Looks like you should check the
tnsnames.ora
file to make sure the listeners are identified properly.Potentially, depends on how/what the package is doing.
If you make changes to the system variables it's best to restart the server.