linked server 测试是否连接

[仙剑]

我想实现这样的功能：
先测试linkedserver是否连通，如果连通，则通过linkedserver执行Insert操作，如果不能连接（比如断网了），给出一条错误消息。

我的代码如下：

use [VCLogIMP]
go
declare @linkserverstate int
declare @linkedserver nvarchar(50)

declare @refid nvarchar(32)
declare @voiceip nvarchar(16)
declare @rootdisk nvarchar(4)
declare @voiceid int
declare @channel int
declare @starttime datetime
declare @stoptime datetime
declare @recordlength int
declare @agent nvarchar(10)
declare @caller nvarchar(50)
declare @called nvarchar(50)
declare @extension nvarchar(20)
declare @direction nvarchar(1)
declare @callerD nvarchar(20)
declare @calledD nvarchar(20)
declare @fileformat int
declare @encryflag nvarchar(1)
declare @inserttime datetime
declare @backupcount int
declare @deletetime datetime
declare @deleteflag nvarchar(1)
declare @deleteidentify nvarchar(20)
declare @reservedone nvarchar(1)
declare @reservedtwo nvarchar(16)
declare @reservedthree nvarchar(16)
declare @reservedfour nvarchar(16)
declare @copyflag int

set @linkedserver ='LS_RECORDDATA'

set @linkserverstate=0
begin try
begin
exec @linkserverstate=sp_testlinkedserver @linkedserver=@linkedserver

if @linkserverstate=0
begin
select 'connected'
--select * from LS_RECORDDATA.VCLogIMP.dbo.RecordOriginalData
declare myCursor cursor for
SELECT [RecordReference]
,[VoiceIP]
,[RootDisk]
,[voiceId]
,[Channel]
,[StartRecordTime]
,[StopRecordTime]
,[RecordLength]
,[AgentID]
,[CallerID]
,[CalledID]
,[Extension]
,[DirectionFlag]
,[CallerDTMF]
,[CalledDTMF]
,[FileFormat]
,[EncryFlag]
,[InsertTime]
,[BackupCount]
,[DeleteFlag]
,[DeleteTime]
,[DeleteIdentify]
,[ReservedOne]
,[ReservedTwo]
,[ReservedThree]
,[ReservedFour]
,[CopyFlag]
FROM [dbo].[T_RecordData] where [CopyFlag]<>-1

open myCursor
fetch next from myCursor into @refid
,@voiceip
,@rootdisk
,@voiceid
,@channel
,@starttime
,@stoptime
,@recordlength
,@agent
,@caller
,@called
,@extension
,@direction
,@callerD
,@calledD
,@fileformat
,@encryflag
,@inserttime
,@backupcount
,@deleteflag
,@deletetime
,@deleteidentify
,@reservedone
,@reservedtwo
,@reservedthree
,@reservedfour
,@copyflag
while @@fetch_status=0

begin
--已经存在，修改同步标记
if exists(select COUNT(*) from [LS_RECORDDATA].[VCLogIMP].[dbo].[RecordOriginalData] where RecordReference=@refid)
begin
begin try
update [dbo].[T_RecordData] set [CopyFlag]=-1 where [RecordReference]=@refid
end try
begin catch
select ERROR_MESSAGE()
end catch
end
else
begin
begin try
--插入新纪录
insert into [LS_RECORDDATA].[VCLogIMP].[dbo].[RecordOriginalData]([RecordReference]
,[VoiceIP]
,[RootDisk]
,[voiceId]
,[Channel]
,[StartRecordTime]
,[StopRecordTime]
,[RecordLength]
,[AgentID]
,[CallerID]
,[CalledID]
,[Extension]
,[DirectionFlag]
,[CallerDTMF]
,[CalledDTMF]
,[FileFormat]
,[EncryFlag]
,[InsertTime]
,[BackupCount]
,[DeleteFlag]
,[DeleteTime]
,[DeleteIdentify]
,[ReservedOne]
,[ReservedTwo]
,[ReservedThree]
,[ReservedFour])
values(@refid
,@voiceip
,@rootdisk
,@voiceid
,@channel
,@starttime
,@stoptime
,@recordlength
,@agent
,@caller
,@called
,@extension
,@direction
,@callerD
,@calledD
,@fileformat
,@encryflag
,@inserttime
,@backupcount
,@deleteflag
,@deletetime
,@deleteidentify
,@reservedone
,@reservedtwo
,@reservedthree
,@reservedfour)

--同步成功，修改同步标记 -1
begin try
update [dbo].[T_RecordData] set [CopyFlag]=-1 where [RecordReference]=@refid
--commit transaction
end try
begin catch
--更新同步标记失败,录音记录=@refid,回滚
--rollback transaction
select ERROR_MESSAGE()
end catch
end try
begin catch
--同步失败，修改同步标记 ,同步次数递增
begin try
update [dbo].[T_RecordData] set [CopyFlag]=(@copyflag+1) where [RecordReference]=@refid
--commit transaction
end try
begin catch
--更新同步标记失败,录音记录=@refid,回滚
--rollback transaction
select ERROR_MESSAGE()
end catch
end catch
end
--下一条纪录
fetch next from myCursor into @refid
,@voiceip
,@rootdisk
,@voiceid
,@channel
,@starttime
,@stoptime
,@recordlength
,@agent
,@caller
,@called
,@extension
,@direction
,@callerD
,@calledD
,@fileformat
,@encryflag
,@inserttime
,@backupcount
,@deleteflag
,@deletetime
,@deleteidentify
,@reservedone
,@reservedtwo
,@reservedthree
,@reservedfour
,@copyflag
end

close myCursor
deallocate myCursor

begin try
delete from [dbo].[T_RecordData] where [CopyFlag] =-1
end try
begin catch
select ERROR_MESSAGE()
end catch
end
end
end try
begin catch
exec dbo.p_WriteHistory 'Connect Server',1,'Server:LS_RECORDDATA(192.168.4.4.40)',@@ERROR
select 'connect error'
end catch

我是用TRY CATCH语句，当连接不上远程服务器时就用存储过程P_WriteHistory向表中插入一条错误消息，但实际上当连接不上远程服务器时并不转入CACTCH语言块，而是直接报错，而且执行时间通常是40到60秒。

请高手帮我分析一下，不胜感激呀！

[--小F--]

太长了 请贴出你认为会出问题的模块吧。

[--小F--]

大版的意思是在写一个事务的时候
begin distributed tran
...
commit tran

[--小F--]

这样的话就需要

启动两台服务器的MSDTC服务
MSDTC服务提供分布式事务服务，如果要在数据库中使用分布式事务，必须在参与的双方服务器启动MSDTC（Distributed Transaction Coordinator）服务。

打开双方的135端口
MSDTC服务依赖于RPC（Remote Procedure Call (RPC)）服务,RPC使用135端口，保证RPC服务启动，如果服务器有防火墙，保证135端口不被防火墙挡住。
使用“telnet IP 135”命令测试对方端口是否对外开放。也可用端口扫描软件（比如Advanced Port Scanner）扫描端口以判断端口是否开放

[仙剑]

事务处理我可以不考虑，我只需要在连接不上的时候（比如断网）转到CATCH语句就行了，可实际上却不行（有时在执行40秒后可以转到Catch，只出现了几次，大多数都是直接给出错误消息，并没有走Catch

我把我的代码简化了，把连接成功后的一系列操作去掉，只做一次Select操作（以上代码这句注释了），这样的程序能正常执行（有几次是不行的），正常的话，如果我连接不上连接服务器，那么之后的程序都不会执行的呀，为什么两次测试的结果却不一样，实在奇快了

[仙剑]

下面我把一些代码注释掉

use [VCLogIMP]
go

declare @linkserverstate int
declare @linkedserver nvarchar(50)

--Insert statements for procedure here
declare @refid nvarchar(32)
declare @voiceip nvarchar(16)
declare @rootdisk nvarchar(4)
declare @voiceid int
declare @channel int
declare @starttime datetime
declare @stoptime datetime
declare @recordlength int
declare @agent nvarchar(10)
declare @caller nvarchar(50)
declare @called nvarchar(50)
declare @extension nvarchar(20)
declare @direction nvarchar(1)
declare @callerD nvarchar(20)
declare @calledD nvarchar(20)
declare @fileformat int
declare @encryflag nvarchar(1)
declare @inserttime datetime
declare @backupcount int
declare @deletetime datetime
declare @deleteflag nvarchar(1)
declare @deleteidentify nvarchar(20)
declare @reservedone nvarchar(1)
declare @reservedtwo nvarchar(16)
declare @reservedthree nvarchar(16)
declare @reservedfour nvarchar(16)
declare @copyflag int

set @linkedserver ='LS_RECORDDATA'

set @linkserverstate=0
begin try
begin
exec @linkserverstate=sp_testlinkedserver @linkedserver=@linkedserver

if @linkserverstate=0
begin
select 'connected'
--select * from LS_RECORDDATA.VCLogIMP.dbo.RecordOriginalData
--declare myCursor cursor for
--SELECT [RecordReference]
-- ,[VoiceIP]
-- ,[RootDisk]
-- ,[voiceId]
-- ,[Channel]
-- ,[StartRecordTime]
-- ,[StopRecordTime]
-- ,[RecordLength]
-- ,[AgentID]
-- ,[CallerID]
-- ,[CalledID]
-- ,[Extension]
-- ,[DirectionFlag]
-- ,[CallerDTMF]
-- ,[CalledDTMF]
-- ,[FileFormat]
-- ,[EncryFlag]
-- ,[InsertTime]
-- ,[BackupCount]
-- ,[DeleteFlag]
-- ,[DeleteTime]
-- ,[DeleteIdentify]
-- ,[ReservedOne]
-- ,[ReservedTwo]
-- ,[ReservedThree]
-- ,[ReservedFour]
-- ,[CopyFlag]
--FROM [dbo].[T_RecordData] where [CopyFlag]<>-1

--open myCursor
--fetch next from myCursor into @refid
--,@voiceip
--,@rootdisk
--,@voiceid
--,@channel
--,@starttime
--,@stoptime
--,@recordlength
--,@agent
--,@caller
--,@called
--,@extension
--,@direction
--,@callerD
--,@calledD
--,@fileformat
--,@encryflag
--,@inserttime
--,@backupcount
--,@deleteflag
--,@deletetime
--,@deleteidentify
--,@reservedone
--,@reservedtwo
--,@reservedthree
--,@reservedfour
--,@copyflag
--while @@fetch_status=0

--begin
-- begin distributed transaction
-- --已经存在，修改同步标记
-- if exists(select COUNT(*) from [LS_RECORDDATA].[VCLogIMP].[dbo].[RecordOriginalData] where RecordReference=@refid)
-- begin
-- begin try
-- update [dbo].[T_RecordData] set [CopyFlag]=-1 where [RecordReference]=@refid
-- end try
-- begin catch
-- select ERROR_MESSAGE()
-- end catch
-- end
-- else
-- begin
-- begin try
-- --插入新纪录
-- insert into [LS_RECORDDATA].[VCLogIMP].[dbo].[RecordOriginalData]([RecordReference]
-- ,[VoiceIP]
-- ,[RootDisk]
-- ,[voiceId]
-- ,[Channel]
-- ,[StartRecordTime]
-- ,[StopRecordTime]
-- ,[RecordLength]
-- ,[AgentID]
-- ,[CallerID]
-- ,[CalledID]
-- ,[Extension]
-- ,[DirectionFlag]
-- ,[CallerDTMF]
-- ,[CalledDTMF]
-- ,[FileFormat]
-- ,[EncryFlag]
-- ,[InsertTime]
-- ,[BackupCount]
-- ,[DeleteFlag]
-- ,[DeleteTime]
-- ,[DeleteIdentify]
-- ,[ReservedOne]
-- ,[ReservedTwo]
-- ,[ReservedThree]
-- ,[ReservedFour])
-- values(@refid
-- ,@voiceip
-- ,@rootdisk
-- ,@voiceid
-- ,@channel
-- ,@starttime
-- ,@stoptime
-- ,@recordlength
-- ,@agent
-- ,@caller
-- ,@called
-- ,@extension
-- ,@direction
-- ,@callerD
-- ,@calledD
-- ,@fileformat
-- ,@encryflag
-- ,@inserttime
-- ,@backupcount
-- ,@deleteflag
-- ,@deletetime
-- ,@deleteidentify
-- ,@reservedone
-- ,@reservedtwo
-- ,@reservedthree
-- ,@reservedfour)

-- --同步成功，修改同步标记 -1
-- begin try
-- update [dbo].[T_RecordData] set [CopyFlag]=-1 where [RecordReference]=@refid
-- --commit transaction
-- end try
-- begin catch
-- --更新同步标记失败,录音记录=@refid,回滚
-- --rollback transaction
-- select ERROR_MESSAGE()
-- end catch
-- end try
-- begin catch
-- --同步失败，修改同步标记 ,同步次数递增
-- begin try
-- update [dbo].[T_RecordData] set [CopyFlag]=(@copyflag+1) where [RecordReference]=@refid
-- --commit transaction
-- end try
-- begin catch
-- --更新同步标记失败,录音记录=@refid,回滚
-- --rollback transaction
-- select ERROR_MESSAGE()
-- end catch
-- end catch
-- end
-- commit transaction
-- --下一条纪录
-- fetch next from myCursor into @refid
-- ,@voiceip
-- ,@rootdisk
-- ,@voiceid
-- ,@channel
-- ,@starttime
-- ,@stoptime
-- ,@recordlength
-- ,@agent
-- ,@caller
-- ,@called
-- ,@extension
-- ,@direction
-- ,@callerD
-- ,@calledD
-- ,@fileformat
-- ,@encryflag
-- ,@inserttime
-- ,@backupcount
-- ,@deleteflag
-- ,@deletetime
-- ,@deleteidentify
-- ,@reservedone
-- ,@reservedtwo
-- ,@reservedthree
-- ,@reservedfour
-- ,@copyflag
--end

--close myCursor
--deallocate myCursor

--begin try
-- delete from [dbo].[T_RecordData] where [CopyFlag] =-1
--end try
--begin catch
-- select ERROR_MESSAGE()
--end catch
end
end
end try
begin catch
--exec dbo.p_WriteHistory 'Connect Server',1,'Server:LS_RECORDDATA(192.168.4.4.40)',@@ERROR
select 'connect error'
end catch

一般执行40到60秒，是正常的

另外我不清楚这个执行时间，能不能把这个时间设短些呢

[中国风]

用分佈式事務BEGIN DISTRIBUTED TRANSACTION;試試

[仙剑]

[Quote=引用 1 楼 roy_88 的回复:]

用分佈式事務BEGIN DISTRIBUTED TRANSACTION;試試
[/Quote]

楼上的朋友麻烦说详细一点，俺是菜鸟，分布式事务有什么作用，能说明一下我出错的原因吗，谢谢！