1. 在SQL Server所在的服务器安装Oracle客户端软件
2. 配置tnsnames.ora文件,示例如下:
MYDB =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = mydb)
)
)
3. 在SQL Server建立Linkserver,可通过如下脚本实现:
[sql] view plain copy
- EXEC master.dbo.sp_addlinkedserver @server = N'linkoracle',
- @srvproduct = N'oracle', @provider = N'OraOLEDB.Oracle',
- @datasrc = N'mydb'
- EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname = N'linkoracle',
- @useself = N'False', @locallogin = N'sa', @rmtuser = N'wxy',
- @rmtpassword = '123456'
之后可以通过OPENQUERY的方式访问Oracle的数据:
[sql] view plain copy
- SELECT *
- FROM OPENQUERY(linkoracle, 'select * from tab');