首先先要对SQL Server dtc 配置
- 打开“控制面板―管理工具―组件服务”(或者运行“Dcomcnfg.exe”)
- 打开“组件服务―计算机”
- 在“我的电脑”上点击右键,点击“属性”
- 在MSDTC选项卡中,点击“安全配置”按钮。
操作如下:
SET XACT_ABORT ON 的设置
当为ON时,如果你存储中的某个地方出了问题,整个事务中的语句都会回滚为OFF时,只回滚错误的地方
跨库操作:
- exec sp_addlinkedserver 'server_link','','SQLOLEDB','192.168.1.82' --创建服务器
- exec sp_addlinkedsrvlogin 'server_link','false',null,'sa','a@1'--设置服务器链接
- exec sp_serveroption 'server_link','rpc out','true' --这个允许调用链接服务器上的存储过程
- go
- exec sp_helpserver--查询服务器
- exec sp_dropserver 'server_link','droplogins'--删除服务器
- select * from server_link.UCenter.dbo.Site
- select * from server_link.UCenter.dbo.Member
- --临时创建跨服务器
- select * from OPENDATASOURCE('SQLOLEDB','Data Source=192.168.1.82;User ID=sa;Password=a@1').UCenter.dbo.Site
- --启用Ad Hoc Distributed Queries
- exec sp_configure 'show advanced options',1
- reconfigure
- exec sp_configure 'Ad Hoc Distributed Queries',1
- reconfigure
- --关闭Ad Hoc Distributed Queries
- exec sp_configure 'Ad Hoc Distributed Queries',0
- reconfigure
- exec sp_configure 'show advanced options',0
- reconfigure
exec sp_addlinkedserver 'server_link','','SQLOLEDB','192.168.1.82' --创建服务器 exec sp_addlinkedsrvlogin 'server_link','false',null,'sa','a@1'--设置服务器链接 exec sp_serveroption 'server_link','rpc out','true' --这个允许调用链接服务器上的存储过程 go exec sp_helpserver--查询服务器 exec sp_dropserver 'server_link','droplogins'--删除服务器 select * from server_link.UCenter.dbo.Site select * from server_link.UCenter.dbo.Member --临时创建跨服务器 select * from OPENDATASOURCE('SQLOLEDB','Data Source=192.168.1.82;User ID=sa;Password=a@1').UCenter.dbo.Site --启用Ad Hoc Distributed Queries exec sp_configure 'show advanced options',1 reconfigure exec sp_configure 'Ad Hoc Distributed Queries',1 reconfigure --关闭Ad Hoc Distributed Queries exec sp_configure 'Ad Hoc Distributed Queries',0 reconfigure exec sp_configure 'show advanced options',0 reconfigure
我们需要在存储过程中跨库操作另一个存储过程这里开始事务使用:begin distributed transaction
- use NorthWind --远程数据库
- go
- alter procedure pro_pro1
- @Name varchar(16),
- @ReturnState int output
- as
- begin transaction test1
- insert into dbo.Categories(CategoryName)values(@Name)
- if(@@ERROR <> 0)
- begin
- print @@IDENTITY
- set @ReturnState = @@IDENTITY
- rollback transaction test1
- end
- else
- begin
- set @ReturnState = 0
- commit transaction test1
- end
- go
- --在本地创建
- --创建远程链接服务
- exec sp_addlinkedserver 'srv_lnk','','SQLOLEDB','WIN-QL1C52TNRPO\SQLSERVER_2008'
- exec sp_addlinkedsrvlogin 'srv_lnk','false',null,'sa','817708'
- exec sp_serveroption 'srv_lnk','rpc out','true' --这个允许调用链接服务器上的存储过程
- go
- EXEC master.dbo.sp_serveroption @server=N'LnkDW', @optname=N'rpc', @optvalue=N'true'
- GO
- EXEC master.dbo.sp_serveroption @server=N'LnkDW', @optname=N'rpc out', @optvalue=N'true'
- GO
- use HR_DB --本地数据库
- go
- alter procedure pro_pro2
- @Name varchar(15),
- @u_name varchar(60),
- @result int output,
- @ReturnState int output
- as
- --对于远程事务 on全部回滚 off回滚错误语句
- set xact_abort on
- --开启远程事务
- begin distributed transaction test2
- insert into dbo.bonus(major_kind_name) values(@u_name)
- if(@@ERROR <> 0)
- begin
- set @ReturnState = 1
- rollback transaction test2
- end
- else
- begin
- set @ReturnState = 0
- --跨库执行存储过程
- exec srv_lnk.NorthWind.dbo.pro_pro1 @Name,@result
- commit transaction test2
- end
- go
- exec srv_lnk.HR_DB.dbo.pro_pro2 '','','',''
- --删除链接
- exec sp_dropserver 'srv_lnk','droplogins'
- go
- declare @result int , @ReturnState int
- exec pro_pro2 'fffffffffffffff','ffffffffffffffff',@result,@ReturnState
use NorthWind --远程数据库 go alter procedure pro_pro1 @Name varchar(16), @ReturnState int output as begin transaction test1 insert into dbo.Categories(CategoryName)values(@Name) if(@@ERROR <> 0) begin print @@IDENTITY set @ReturnState = @@IDENTITY rollback transaction test1 end else begin set @ReturnState = 0 commit transaction test1 end go --在本地创建 --创建远程链接服务 exec sp_addlinkedserver 'srv_lnk','','SQLOLEDB','WIN-QL1C52TNRPO\SQLSERVER_2008' exec sp_addlinkedsrvlogin 'srv_lnk','false',null,'sa','817708' exec sp_serveroption 'srv_lnk','rpc out','true' --这个允许调用链接服务器上的存储过程 go EXEC master.dbo.sp_serveroption @server=N'LnkDW', @optname=N'rpc', @optvalue=N'true' GO EXEC master.dbo.sp_serveroption @server=N'LnkDW', @optname=N'rpc out', @optvalue=N'true' GO use HR_DB --本地数据库 go alter procedure pro_pro2 @Name varchar(15), @u_name varchar(60), @result int output, @ReturnState int output as --对于远程事务 on全部回滚 off回滚错误语句 set xact_abort on --开启远程事务 begin distributed transaction test2 insert into dbo.bonus(major_kind_name) values(@u_name) if(@@ERROR <> 0) begin set @ReturnState = 1 rollback transaction test2 end else begin set @ReturnState = 0 --跨库执行存储过程 exec srv_lnk.NorthWind.dbo.pro_pro1 @Name,@result commit transaction test2 end go exec srv_lnk.HR_DB.dbo.pro_pro2 '','','','' --删除链接 exec sp_dropserver 'srv_lnk','droplogins' go declare @result int , @ReturnState int exec pro_pro2 'fffffffffffffff','ffffffffffffffff',@result,@ReturnState
执行如果都成功的话 则会提交,如果其中有一个执行失败的话,执行结果会显示影响数,但数据库并没有此数据。