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.