SQL Server 分布式存储过程事务

首先先要对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

  
  我们需要在存储过程中跨库操作另一个存储过程

  这里开始事务使用：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

  
  执行如果都成功的话 则会提交，如果其中有一个执行失败的话，执行结果会显示影响数，但数据库并没有此数据。