sqlserver连接oracle

1. 安装oracle精简客户端Instant Client SetupX64.exe，勾选 ODBC Driver

2. ODBC添加数据源，注意是在： 系统DSN ，没有显示oracle驱动则重启服务器

3. TNS Service Name： 192.168.2.18:1521/DLORCL

4. sqlserver新建链接服务器，访问接口字符串：MSDASQL

5. 输入oracle的用户名、密码

6. 将RPC，RPC out 设置为true

7. 查询测试

--SELECT
select * from openquery(Orcl,'select * from dept');
select * from openquery(Orcl,'select * from dept where deptno > 10');
select * from openquery(Orcl,'select * from dept') where deptno > 10;
--测试不成功
select * from ORCL..SCOTT.DEPT; ---表名一定要用大写，不然会出现表找不到的错误。
select * from ORCL..SCOTT.DEPT where deptno > 10;
--UPDATE
update openQuery(orcl,'select * from dept where deptno = 10') set dname = 'New Name';
update openQuery(orcl,'select * from dept') set dname = 'New Name2' where deptno = 10;
--INSERT
insert into openquery(orcl,'select deptno,dname,Loc from dept where 1=0') values (50,'newDept','loc');
insert into openquery(orcl,'select deptno,dname from dept where 1=0') values (60,'newDept');
--DELETE
delete from openquery(orcl,'select deptno from dept where deptno=60');
delete from openquery(orcl,'select deptno from dept') where deptno=50;