A thought of SQL Server and Oracle data Mutual guidance--sql server linked servers

Idea: You can remotely query the table of an Oracle database by adding a linked server to the SQL Server database
Environment preparation, install the SQL Server database, install the Oracle driver, configure the Oracle connection in the configured Tnsname file, I use the SQL server2008 r2,oracle11g test, Provider adoption of the OraOLEDB.Oracle (many articles on the web said this could not be successful, to choose Microsoft OLE DB privider for Oracle, I chose this configuration is successful, it is estimated that there can be no more than two, if there are two, SQL Server default with Microsoft's)

The Tnsmae configuration is as follows:

Orcl11 = (DESCRIPTION = (Address_list = (ADDRESS = (PROTOCOL = TCP) (HOST = WILSONPC3) (PORT = 1521))) (Connect_data = (service_ NAME = orcl11)))

　　

First, make sure that the SQL Server machine is connected to Oracle via Sqlplus

Add Oracle access to the SQL Server linked server named Oracl

Querying the Oracle database remotely in SQL Server Management Studio

SELECT * FROM OPENQUERY (oracl, ' select Empno,ename,job,mgr,hiredate,sal,comm,deptno from emp ') Ainner join EMP Bon a.empno =b.empnoand b.empno=7369

　　

The OPENQUERY result is equivalent to returning a remote table object that can be used as a link to the SQL statement, or an update delete operation on the Oracle data, for example, the following code inserts a remote Oralcle database:

Insert INTO OPENQUERY (Oracl, ' select Empno,ename,job,mgr,hiredate,sal,comm,deptno from emp ') SELECT [empno]+1000,[ Ename],[job],[mgr],[hiredate],[sal],[comm],[deptno]from EMP

　　

Delete operation:

Delete from OPENQUERY (Oracl, ' select Empno,ename,job,mgr,hiredate,sal,comm,deptno from emp ') where empno= ' 7369 '

With the above methods of operation, SQL Server and Oracle data Mutual guidance has a basic idea.

A thought of SQL Server and Oracle data Mutual guidance--sql server linked servers