前提:sqlserver服务器需要安装oracle客户端程序
一,步骤:
--创建oracle连接
EXEC master.dbo.sp_addlinkedserver
@server = N'SERVER_LINKNAME',
@srvproduct=N'ORACLE',
@provider=N'OraOLEDB.Oracle',
@datasrc=N'dblink' -- 连接服务名 如果不像配置tnsname,可以使用ip:端口/服务名
--创建认证方式
EXEC master.dbo.sp_addlinkedsrvlogin
@rmtsrvname=N'SERVER_LINKNAME',
@useself=N'False',
@locallogin=NULL,
@rmtuser=N'uname',
@rmtpassword='XXX'
如果创建的连接成功,而没有访问权限,则设置访问接口的访问权限即可
EXEC master.dbo.sp_MSset_oledb_prop N'OraOLEDB.Oracle', N'AllowInProcess', 1
二,访问
--查询操作
SELECT * FROM OPENQUERY(ORCL, 'select * from test')
--事务操作需要开启windows上的服务:MSDTC(Distributed Transaction Coordinator)
--删除操作
begin transaction
SET XACT_ABORT on
DELETE FROM OPENQUERY(ORCL, 'select * from test t where t.id=1')
rollback
SET XACT_ABORT off
--插入操作
INSERT INTO OPENQUERY (ORCL,'select * from test')
(id,name,remark,n1) VALUES (1,123,'china',1.23)
--更新操作
UPDATE OPENQUERY (orcl,'select * from test') SET remark='shenzhen' WHERE id=1
-- 调用过程
SELECT * FROM OPENQUERY(orcl,'SELECT U1.F_1() FROM DUAL')
-- 另外一种形式的查询,不过 数据类型不一致时经常报错
select * from orcl..u1.T1;
创建其他数据库连接
-- sqlserver
exec sp_addlinkedserver @server='BOAD',@srvproduct='',@provider='SQLOLEDB',@datasrc='远程服务器名或ip地址'
exec sp_addlinkedsrvlogin 'BOAD','false', NULL,'user','password'
--查询示例
select* from BOAD.DBNAME.dbo.A
问题1,访问oracle时候报错,sqlserver 错误7302
解决:将SQL Server(MSSQLSERVER)和SQL Full-Filter Deamon Launcher(MSSQLSERVER)两个服务的登录身份改为Local Service
问题2,访问oracle时候报错,sqlserver 错误7402
解决:缺少驱动,可以通过安装oracle客户端安装ole db驱动,注意位数