Linked Server along Oracle 32 and 64 bit Oracle Client

Question

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:

1. 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?

2. 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

3. Is the order in which oracle bin path is defined in PATH System Environment variable can impact this issue in anyway?

4. 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.

Answer 1

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?

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.

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

Looks like you should check the tnsnames.ora file to make sure the listeners are identified properly.

Is the order in which oracle bin path is defined in PATH System Environment variable can impact this issue in anyway?

Potentially, depends on how/what the package is doing.

After making the changes in PATH System Environment variable do we need to restart SQL Server service or entire server.

If you make changes to the system variables it's best to restart the server.