我想实现这样的功能:
先测试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 intset @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秒。请高手帮我分析一下,不胜感激呀!
先测试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 intset @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秒。请高手帮我分析一下,不胜感激呀!
大版的意思是在写一个事务的时候
begin distributed tran
...
commit tran
MSDTC服务提供分布式事务服务,如果要在数据库中使用分布式事务,必须在参与的双方服务器启动MSDTC(Distributed Transaction Coordinator)服务。打开双方的135端口
MSDTC服务依赖于RPC(Remote Procedure Call (RPC))服务,RPC使用135端口,保证RPC服务启动,如果服务器有防火墙,保证135端口不被防火墙挡住。
使用“telnet IP 135”命令测试对方端口是否对外开放。也可用端口扫描软件(比如Advanced Port Scanner)扫描端口以判断端口是否开放
godeclare @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 intset @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秒,是正常的另外我不清楚这个执行时间,能不能把这个时间设短些呢