SQL Server连接Oracle详细步骤

在SQL Server  Management Studio 中操作oracle数据库：

1. 配置oracle数据源

2.在sqlserver中创建连接服务器

查看sqlserver代码如下：

EXEC master.dbo.sp_addlinkedserver @server = N'ORCL_LS', @srvproduct=N'oracle', @provider=N'OraOLEDB.Oracle', @datasrc=N'orcl'
GO
EXEC master.dbo.sp_serveroption @server=N'ORCL_LS', @optname=N'collation compatible', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'ORCL_LS', @optname=N'data access', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'ORCL_LS', @optname=N'dist', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'ORCL_LS', @optname=N'pub', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'ORCL_LS', @optname=N'rpc', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'ORCL_LS', @optname=N'rpc out', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'ORCL_LS', @optname=N'sub', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'ORCL_LS', @optname=N'connect timeout', @optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @server=N'ORCL_LS', @optname=N'collation name', @optvalue=null
GO
EXEC master.dbo.sp_serveroption @server=N'ORCL_LS', @optname=N'lazy schema validation', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'ORCL_LS', @optname=N'query timeout', @optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @server=N'ORCL_LS', @optname=N'use remote collation', @optvalue=N'true'

上面使用的访问接口为：Oracle Provider for OLE DB

还有一种方法创建，使用接口为： Microsoft OLE DB Provider ODBC Driver

3.执行查询

select * from openquery(ORCL_LS,'select * from SCOTT.EMP');

出现错误：
无法对链接服务器 "ORCL_LS" 的 OLE DB 访问接口 "OraOLEDB.Oracle" 执行查询"select * from SCOTT.EMP"。

解决：链接服务器->访问接口->OraOLEDB.Oracle->右键属性，选中 "Allow inprocess" (中文为：允许进程内)

4.其他相关操作（看图）

a.插入

b.更新

c.删除

/*代码如下*/

SELECT * FROM OPENQUERY(ORCL_LS,'SELECT * FROM SCOTT.DEPT');

INSERT OPENQUERY (ORCL_LS, 'SELECT DEPTNO,DNAME,LOC FROM SCOTT.DEPT') VALUES (50,'NewDEPT','NewLOC');

UPDATE OPENQUERY (ORCL_LS, 'SELECT LOC FROM SCOTT.DEPT WHERE DEPTNO = 50') SET LOC = 'UpdateLOC';

DELETE OPENQUERY (ORCL_LS, 'SELECT DEPTNO FROM SCOTT.DEPT WHERE DEPTNO = 50');

数据库配置参考：Oracle Windows ODBC 数据源配置