测试目的：Oracle database connect MSSQL 2012
测试环境：1. Oracle 11gR2 64bit On linux
2. SQL Server 2012 64 bit on Windows 7
测试步骤：
Step 1: install ORACLE ODAC 64bit. You shouldn't install the oracle client for windows. Just only download and install ODAC on the server which is running SQL Server. If your SQL Server is for 32bit, you must download 32bit ODAC. The download site is http://www.oracle.com/technetwork/database/windows/downloads/index-101290.html
Step 2: configure the TNS on oracle home directory. Because ODAC is components which is used for connecting ORACLE database, the NETMGR and NETCA is not installed on the server. So, you should configure Oracle TRANSNAMES file by manual. The simply way is copy it from other server and reconfigure.
Step 3: restart the MSSQLSERVER service to register the Oracle provider.
Step 4: Open SSMS to create DB Links for oracle on MSSQL. Check the provide and you can find the new provider
If you don't want to use command to create oracle DB Links, you can use the graph interface.
Linked Server : Create new DB Link Name what you want;
Privider : Choice Oracle Provider for OLE DB
Data source: the TNS name which you create at the last steps.
Step 5: input the username and password for connecting Oracle.
Step 6: Test the DB Links
In normal way,the error is displayed if we query the table in Oracle database using [db_link].[database].[schema].[table].
the error is :
Msg 7312, Level 16, State 1, Line 1
Invalid use of schema or catalog for OLE DB provider "OraOLEDB.Oracle" for linked server "ORCL_A01". A four-part name was supplied, but the provider does not expose the necessary interfaces to use a catalog or schema.
After changes the table name to [db_link]..[schema].[table] format, the query can be worked.