問題描述
我在從鏈接服務(wù)器調(diào)用存儲過程時遇到問題，但它超時了.但是我沒有很好的方法來捕捉這個.雖然很少發(fā)生，但我想知道是否有任何方法可以捕獲此特定警告:
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:
鏈接服務(wù)器serverName"的 OLE DB 提供程序SQLNCLI10"返回消息查詢超時已過期".
OLE DB provider "SQLNCLI10" for linked server "serverName" returned message "Query timeout expired".
不幸的是，try/catch 沒有捕獲警告，而且 MS 確實有一個未解決的問題，這應(yīng)該是一個錯誤:http://connect.microsoft.com/SQLServer/feedback/details/337043/no-error-raised-when-遠(yuǎn)程程序超時
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
我不想增加超時屬性，而且我知道我可以執(zhí)行以下操作:
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 之后為空，則表示調(diào)用失敗，但它并沒有告訴我確切的原因是什么.反正有沒有基本上抓住那個警告?遠(yuǎn)程過程調(diào)用錯誤處理的最佳實踐是什么?
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 遠(yuǎn)程超時錯誤.
As of 2019 there is still no way to properly catch SQL Server remote timeout errors.
它既適用于遠(yuǎn)程 SP 調(diào)用，也適用于 REMOTESQLSERVER 上的 execute ('select 1') 等構(gòu)造.
It applies both to remote SP calls and constructs like execute ('select 1') at REMOTESQLSERVER.
根據(jù) 來自 N.Nelu 的評論:
Microsoft 文檔 在不受 TRY...CATCH 構(gòu)造影響的錯誤"下進(jìn)行了說明.
Microsoft docs state under "Errors Unaffected by a TRY...CATCH Construct".
不受 TRY...CATCH 結(jié)構(gòu)影響的錯誤
TRY...CATCH 結(jié)構(gòu)不會捕獲以下條件:
TRY...CATCH constructs do not trap the following conditions:
嚴(yán)重性為 10 或較低.
Warnings or informational messages that have a severity of 10 or lower.
嚴(yán)重性為 20 或更高的錯誤會停止會話的 SQL Server 數(shù)據(jù)庫引擎任務(wù)處理.如果出錯發(fā)生嚴(yán)重性為 20 或更高且數(shù)據(jù)庫連接未中斷，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.
注意，例如客戶端中斷請求或客戶端連接斷開.
系統(tǒng)管理員使用 KILL 語句結(jié)束會話時.
When the session is ended by a system administrator by using the KILL statement.
您提供的連接鏈接已失效，但您仍然可以投票修復(fù)此功能 此處.另請參閱關(guān)于 SQL 錯誤處理的優(yōu)秀文章，位于4.3 鏈接上的查詢超時服務(wù)器.
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.
這篇關(guān)于SQL 捕獲遠(yuǎn)程過程拋出的警告的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網(wǎng)！