将新记录传输到失去连接的链接服务器的存储过程

Question

让我先说一下，我不是sql专家。提前感谢您审阅我的问题。

我有一些麻烦的存储过程，定期失去它的连接到一个链接的服务器在传输新记录到表的中间(很多记录)。我想知道一些事情。

首先，是否有更好的方法来更快更有效地完成任务?

第二，是否有一种方法可以更好地处理连接丢失，试图重新尝试前一步，以努力继续它离开的地方，可能在等待15分钟左右，以防与链接的服务器有一些临时断开连接(可能持续超过终止之前允许的时间)?

我也很好奇，当像突出显示的步骤发生时，如果sql正在对链接的服务器执行，并且连接丢失，sql是否尝试重新连接并继续超时时间，或者因为这是一个比较语句，它只是中断吗?我真的不明白它是怎么工作的。

这是连接丢失最常见的部分，通常发生在第一个插入语句中(开始catch块的第一步):

UPDATE [LINKEDSERVER.XYZ.COM].dest_database1.dbo.run
  SET last_result = 32
  WHERE type = 158;
BEGIN TRY
    INSERT INTO [LINKEDSERVER.XYZ.COM].[dest_database2].dbo.table1 ( CID , BID , Question1 , Question2 , Question3 , Question4 , Question5 , Question6 , Question7 , Question8 , Question9 , Question10 , Comments , EmailAddress , Name , Address , Address2 , City , State , Zip ) 
    SELECT src.CID , src.BID , src.Question1 , src.Question2 , src.Question3 , src.Question4 , src.Question5 , src.Question6 , src.Question7 , src.Question8 , src.Question9 , src.Question10 , src.Comments , src.EmailAddress , src.Name , src.Address , src.Address2 , src.City , src.State , src.Zip 
      FROM table1 AS src LEFT OUTER JOIN [LINKEDSERVER.XYZ.COM].[dest_database2].dbo.table1 AS dst ON src.CID = dst.CID AND src.BID = dst.BID
      WHERE dst.CID IS NULL;
    INSERT INTO [LINKEDSERVER.XYZ.COM].[dest_database2].dbo.table2 ( CID , AccountNumber , Name , Address , Address2 , City , State , Zip , BShort, EmailAddress ) 
    SELECT src.CID , src.AccountNumber , src.Name , src.Address , src.Address2 , src.City , src.State , src.Zip , src.BShort, src.EmailAddress 
      FROM table2 AS src LEFT OUTER JOIN [LINKEDSERVER.XYZ.COM].[dest_database2].dbo.table2 AS dst ON src.BShort = dst.BShort
      WHERE dst.BShort IS NULL;
END TRY
BEGIN CATCH
    SELECT @ErrorCode = @@Error;
    SELECT @ErrorResult = 109;
    SELECT @ErrorMessage = 'Failed Copy ' + CAST ( @ErrorCode AS varchar ) ;
    GOTO ExitWithError;
END CATCH;
INSERT INTO [LINKEDSERVER.XYZ.COM].dest_database1.dbo.system_log ( notes , log_type , source_type , parent_id ) 
VALUES ( 'Copied tables xyz ' , 45 , 58 , 0 ) ;
UPDATE [LINKEDSERVER.XYZ.COM].dest_database1.dbo.run
  SET last_result = 31
  WHERE type = 158;

我明白，如果连接丢失，我的catch块不会执行，所以没有使用ExitWithError。我想我明白当连接丢失时，回滚发生(不确定如何/如果这与链接的服务器一起工作，因为一些记录在发生这种情况时被转移)，它只是退出执行一切-因为既不执行捕获，也不执行整个开始捕获块后的语句。我希望，如果发生这种情况，记录一些东西，以便存储过程再次运行时(设置为每30分钟运行一次，直到成功)，它可以从它离开的地方捡起，因为我在开始时有代码查找最后的状态/日志条目并确定要做什么。有更好的方法来处理这个问题吗?

Answer 1

第一个技巧，如果可以的话，从其他服务器(目标服务器)执行存储过程。换句话说，切换本地服务器和链接服务器。原因是我已经大大提高了从链接服务器到本地表的DML(插入/更新/删除)命令的性能。在链接表中执行DML时，性能很差!

Next有效地使用了id，它们是顺序的吗?您是否可以只取目标服务器上的最大值，而不必在服务器之间连接大型表?

如果你不能指望顺序，那么你可以在源服务器上建立一个临时表并在那里进行过滤，而不是尝试跨表进行过滤。如果只是id，您还可以尝试来回传递nvarchar格式的XML。或者您可以在源上维护一个跟踪表，这样您就可以知道使用插入的OUTPUT已经处理了哪些id。id INTO ....从您的插入语句

最后将你的过程设置为批处理/切割，并在较小的块中执行事务，例如一次SELECT TOP x#的记录。您仍然可以将整个操作放在while循环中，以便在一次执行过程中完成所有操作，但是您可以将事务分成更小、更易于管理的部分。