尝试与SQL Server 2005中的链接服务器的CATCH不起作用 [英] TRY CATCH with Linked Server in SQL Server 2005 Not Working

问题描述

我正在尝试捕获在链接服务器上执行存储过程时出现的sql错误.两台服务器都运行SQL Server 2005.

I am trying to catch sql error raised when I execute a stored procedure on a linked server. Both Servers are running SQL Server 2005.

为了证明这个问题，我在链接服务器上创建了一个名为Raise error的存储过程，该存储过程执行以下代码:

To prove the issue I have created a stored procedure on the linked server called Raise error that executes the following code:

RAISERROR('An error', 16, 1);

如果我使用以下代码直接在链接服务器上执行存储过程，则会得到一个带有错误"，"16"的预期结果集(即代码进入catch块):

If I execute the stored procedure directly on the linked server using the following code I get a result set with 'An error', '16' as expected (ie the code enters the catch block):

BEGIN TRY
EXEC [dbo].[RaiseError];
END TRY
BEGIN CATCH
    DECLARE @ErrMsg nvarchar(4000), @ErrSeverity int;
    SELECT @ErrMsg = ERROR_MESSAGE(), @ErrSeverity = ERROR_SEVERITY();
    SELECT @ErrMsg, @ErrSeverity;
END CATCH

如果我在本地服务器上运行以下代码以在链接服务器上执行存储过程，则SSMS给我消息查询已完成，但有错误"，消息50000，级别16，状态1，过程RaiseError，第13行 错误'

If I run the following code on my local server to execute the stored procedure on the linked server then SSMS gives me the message 'Query completed with errors', .Msg 50000, Level 16, State 1, Procedure RaiseError, Line 13 An error'

BEGIN TRY
    EXEC [Server].[Catalog].[dbo].RaiseError
END TRY
BEGIN CATCH
    DECLARE @SPErrMsg nvarchar(4000), @SPErrSeverity int;
    SELECT @SPErrMsg = ERROR_MESSAGE(), @SPErrSeverity = ERROR_SEVERITY();
    SELECT @SPErrMsg, @SPErrSeverity;
END CATCH

我的问题是，我可以捕获链接服务器存储过程执行时生成的错误吗?

My Question is can I catch the error generated when the Linked server stored procedure executes?

提前谢谢！

推荐答案

请参见处理服务器到服务器远程存储过程中的错误:

以更低的严重性调用RAISERROR 从远程存储中存储超过20个 过程导致语句中止 远程服务器上的错误.一种 在本地尝试... CATCH构造 服务器处理远程批处理中止 仅错误.如果远程存储 过程调用RAISERROR与 严重性小于20，且远程 存储过程的范围是 本地服务器上的TRY块， RAISERROR不会导致控制 传递给 TRY…CATCH构造.然而， 严重性为20或更高的RAISERROR 在远程服务器上中断 连接，并在本地执行 服务器传递到CATCH块.

Calling RAISERROR with severity less than 20 from inside a remote stored procedure causes a statement abort error on the remote server. A TRY…CATCH construct on the local server handles remote batch abort errors only. If a remote stored procedure calls RAISERROR with severity less than 20 and the remote stored procedure is scoped within a TRY block on the local server, RAISERROR does not cause control to pass to the CATCH block of the TRY…CATCH construct. However, RAISERROR with severity 20 or greater on the remote server breaks the connection, and execution on the local server passes to the CATCH block.

这篇关于尝试与SQL Server 2005中的链接服务器的CATCH不起作用的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！