問題描述
我在從鏈接服務器調用存儲過程時遇到問題，但它超時了.但是我沒有很好的方法來捕捉這個.雖然很少發生，但我想知道是否有任何方法可以捕獲此特定警告:
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 捕獲遠程過程拋出的警告的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！