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

Question

让我通过说我不是sql专家来作为序言。预先感谢您审查我的问题。

我有一个存储过程周期性地失去了连接到一个链接服务器在传输新记录到一个表（大量的记录）中的一些麻烦。我想知道一些事情。

首先，有没有更好的方法来完成更快，更高效的任务？

10第二，有没有办法更好地处理失败的连接尝试重新尝试上一步，努力继续停止它的位置，可能在等待15分钟左右，以防万一与链接服务器暂时断开连接（可能会持续超过终止前允许的内容）？

此外，我很好奇，当类似的高亮步骤发生，如果正在对链接服务器执行的SQL，并连接丢失，确实SQL尝试重新连接，并继续对超时时间或因为这是一个比较声明，它只是打破？我真的不明白它是如何工作的。

这里是连接丢失是最常见的，一般在第一个INSERT语句发生（第一步内开始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。我想我知道连接丢失时会发生回滚（不知道如何在连接的服务器上工作，因为在发生这种情况时会传输一些记录），并且它只是退出执行所有任务 - 因为这些catch都没有执行或者整个开始catch块之后的语句。我希望在发生这种情况时记录一些事情，以便存储过程在再次运行时（设置为每30分钟运行一次直到成功运行），它可以提取它停止的地方，因为我在开始时有代码它查找最后的状态/日志条目并确定要执行的操作。 有没有更好的方法来处理这个问题？

Answer 1

所以首先，如果可以的话，从其他服务器（目的地）执行存储过程。换句话说，切换本地和链接的服务器。原因是我从链接服务器到本地表的DML（插入/更新/删除）命令的性能有了显着提高。在将DML转换为链接表时，性能会很糟糕！

接下来有效地使用ID，它们是顺序的吗？你可以只用目标服务器上的MAX，而不必在服务器之间加入大型表格？

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

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