SQL 捕获远程过程抛出的警告 [英] SQL Catching a warning thrown by remote procedure

问题描述

我在从链接服务器调用存储过程时遇到问题，但它超时了.但是我没有很好的方法来捕捉这个.虽然很少发生，但我想知道是否有任何方法可以捕获此特定警告:

I have an issue where I call a stored procedure from a linked server and it times out. However I have no good way of catching this. Though it occurs rarely I am wondering if there is any way to catch this particular warning:

链接服务器serverName"的 OLE DB 提供程序SQLNCLI10"返回消息查询超时已过期".

OLE DB provider "SQLNCLI10" for linked server "serverName" returned message "Query timeout expired".

不幸的是，try/catch 没有捕获警告，而且 MS 确实有一个未解决的问题，这应该是一个错误:http://connect.microsoft.com/SQLServer/feedback/details/337043/no-error-raised-when-远程程序超时

Unfortunatly warnings aren't caught by try/catch and MS does have an open issue that this should be an error: http://connect.microsoft.com/SQLServer/feedback/details/337043/no-error-raised-when-a-remote-procedure-times-out

我不想增加超时属性，而且我知道我可以执行以下操作:

I don't want to increase the timeout property, and I know I can do something like:

Declare @ret int
select @ret =  4417
Exec @ret=Server.DB.dbo.RemoteSP

如果@ret 之后为空，则表示调用失败，但它并没有告诉我确切的原因是什么.反正有没有基本上抓住那个警告?远程过程调用错误处理的最佳实践是什么?

If @ret is null afterwards it means the call failed, however it does not tell me exactly what the cause was. Is there anyway to essentially catch that warning? What are the best practices in for remote procedure calls error handling?

推荐答案

截至 2019 年，仍然无法正确捕获 SQL Server 远程超时错误.

As of 2019 there is still no way to properly catch SQL Server remote timeout errors.

它既适用于远程 SP 调用，也适用于 REMOTESQLSERVER 上的 execute ('select 1') 等构造.

It applies both to remote SP calls and constructs like execute ('select 1') at REMOTESQLSERVER.

根据 来自 N.Nelu 的评论:

Microsoft 文档 在不受 TRY...CATCH 构造影响的错误"下进行了说明.

Microsoft docs state under "Errors Unaffected by a TRY...CATCH Construct".

不受 TRY...CATCH 结构影响的错误

TRY...CATCH 结构不会捕获以下条件:

TRY...CATCH constructs do not trap the following conditions:

• 严重性为 10 或较低.

• Warnings or informational messages that have a severity of 10 or lower.

严重性为 20 或更高的错误会停止会话的 SQL Server 数据库引擎任务处理.如果出错发生严重性为 20 或更高且数据库连接未中断，TRY...CATCH 将处理错误.

Errors that have a severity of 20 or higher that stop the SQL Server Database Engine task processing for the session. If an error occurs that has severity of 20 or higher and the database connection is not disrupted, TRY...CATCH will handle the error.

注意，例如客户端中断请求或客户端连接断开.

系统管理员使用 KILL 语句结束会话时.

When the session is ended by a system administrator by using the KILL statement.

您提供的连接链接已失效，但您仍然可以投票修复此功能 此处.另请参阅关于 SQL 错误处理的优秀文章，位于4.3 链接上的查询超时服务器.

Connect link you have provided is dead but you still can vote to fix this feature here. See also this excellent article on SQL Error handling under 4.3 Query Timeout on Linked Servers.

这篇关于SQL 捕获远程过程抛出的警告的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！