链接服务器-分布式事务图解

[b]一、环境说明：[/b]
挑选两台不同系统的电脑测试（XP、Win7、Server2008、Server2003全可）。本例环境如下

本地系统-Win7，数据库名称localTest，IP为192.168.0.102
远端系统-XP，数据库名称Test131，IP为192.168.57.131

[b]二、存储过程[/b]
[b]创建表[/b]

USE [localTest]
GO
/****** Object:  Table [dbo].[City]    Script Date: 06/26/2011 19:37:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[City](
	[CityID] [int] IDENTITY(1,1) NOT NULL,
	[ProvinceID] [int] NOT NULL,
	[CityName] [nvarchar](50) NULL,
 CONSTRAINT [PK_City] PRIMARY KEY CLUSTERED 
(
	[CityID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

[b]存储过程[/b]

USE [localTest]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		牛金明
-- Create date: 2011-6-26
-- Description:	LinkServerTest
-- =============================================
CREATE PROCEDURE [dbo].[LinkServerTest] 
	@prinvinceID int,
	@cityName nvarchar(50)
AS
BEGIN

	SET NOCOUNT ON;
	--返回执行结果成功与否，0失败 1成功
	declare @result int ;

	--解决“无法在此会话中启动更多的事务”错误提示
	set xact_abort ON

	--使用distributed开启分布式事务
	begin  distributed tran  t

	begin try
		--插入远端数据库一条数据
		insert into SV131.Test131.dbo.city(ProvinceID,CityName) 
			values(@prinvinceID,@cityName);

		--此时向本地数据库插入一条数据
		insert into City values(@prinvinceID,@cityName);
		--此条代替向本地数据库插入的一条数据（上面这条数据），用于测试回滚
		--insert into City values(1111111111111111111111111111,@cityName);

		--提交事务
		COMMIT tran t;

		set @result = 1;
    end try
    begin catch
		--回滚事务
		rollback tran t;
		set @result = 0;
    end catch

    --查询两个数据表
    select * from   SV131.Test131.dbo.city
    select * from   city

    --删除数据表数据
    delete from SV131.Test131.dbo.city
	delete from city

	--返回结果
	return @result;
END

--可以使用sql代码创建链接服务器的方法
--EXEC sp_addlinkedserver  'SV131',  N'','SQLOLEDB','192.168.57.131';
--exec sp_addlinkedsrvlogin  'SV131', 'false',null, 'sa', 'njm' 

--查询链接服务器信息
--select * from sys.servers
--删除链接服务器
--sp_dropserver 'SV131','droplogins'

--执行存储过程
--USE [localTest]
--GO
--DECLARE	@return_value int

--EXEC	@return_value = [dbo].[LinkServerTest]
--		@prinvinceID = 11,
--		@cityName = N'22'

--SELECT	'Return Value' = @return_value
--GO

[b]三、配置图解[/b]
[b]本地Win7-链接服务器设置[/b]

[img]http://dl.iteye.com/upload/attachment/504838/ccc574d7-2b5d-3942-a542-b20646a936bc.jpg[/img]


[img]http://dl.iteye.com/upload/attachment/504840/2d988cb3-cde0-3d6f-ae25-4d346ebe42d3.jpg[/img]


[img]http://dl.iteye.com/upload/attachment/504842/cd04e9d1-2208-3a3d-b6a3-0a28358c81c3.jpg[/img]


[img]http://dl.iteye.com/upload/attachment/504844/f1c2fc1a-455d-31af-b705-f8b323b166bf.jpg[/img]

[b]本地Win7-DTC设置[/b]

[img]http://dl.iteye.com/upload/attachment/504846/ea39d986-c897-323b-ac2f-3923d1767ece.jpg[/img]


[img]http://dl.iteye.com/upload/attachment/504848/f0cbf3a9-e190-3505-920a-bfdbfc593793.jpg[/img]


[img]http://dl.iteye.com/upload/attachment/504850/242876c7-7a82-3d79-bafa-18f2b561614c.jpg[/img]

[b]本地Win7-hosts文件设置[/b]
[b]两台机子不在同一个网段内。必须保证可以ping通机子名称，而非机子的IP，因此在hosts文件中添加映射。[/b]
[img]http://dl.iteye.com/upload/attachment/504852/a6a7601f-8440-3e2c-80bd-2e9e4bb14c77.jpg[/img]

[b]远端XP-链接服务器设置[/b]

[img]http://dl.iteye.com/upload/attachment/504854/9bdfedfc-eaac-39c0-a416-0ee0b0cf78e5.jpg[/img]


[img]http://dl.iteye.com/upload/attachment/504856/3f95bccb-2e5c-33f2-a1a2-aabb1800f6cd.jpg[/img]


[img]http://dl.iteye.com/upload/attachment/504858/c0de8cb4-4b6c-32d3-b146-9d89eb6290b4.jpg[/img]


[img]http://dl.iteye.com/upload/attachment/504860/93ad5059-5307-38f0-adae-aa16545b3d35.jpg[/img]

[b]四、可能会出现的问题[/b]
[b]1、不同网段没有hosts文件的映射[/b]

[img]http://dl.iteye.com/upload/attachment/504864/c9ce0641-0f88-35c3-b694-ed401b515288.jpg[/img]

[b]2、事务开始前缺少此句set xact_abort ON[/b]

[img]http://dl.iteye.com/upload/attachment/504866/b4c350d3-4b01-383d-b069-5643817c0543.jpg[/img]

[b]五、总结[/b]
1、整个的配置过程不会超过5分钟，但是缺少某个环节都会导致出现不易理解的错误出现。

2、配置DTC可以使用mssql的界面管理器，也可以书写sql代码建立，结果是一样的。

3、两台电脑必须开启MSDTC服务（Distributed Transaction Coordinator）。另外远端电脑还要开放135端口，对应RPC（Remote Procedure Call）服务，这个服务默认是开户的，但要检查是不是被防火墙拦截了（可考虑关闭防火墙或是将RPC设为例外）。

4、事务开启前使用set xact_abort ON此句。

5、事务开启使用begin distributed tran t（非传统的begin tran t）。