如何捕获从链接服务器返回的错误消息? [英] How to capture error message returned from linked server?
问题描述
如何捕获从链接服务器返回的错误消息?
How can I capture an error message returned from a linked server?
例如,如果我在SQL Server Management Studio中运行以下命令:
As an example, if I run the following command in SQL Server Management Studio:
BEGIN TRY
exec ('select * from xxx') at my_linked_server
END TRY
BEGIN CATCH
print 'ErrorNumber...'+ CAST(ERROR_NUMBER() as varchar)
print 'ErrorSeverity...'+ CAST(ERROR_SEVERITY() as varchar)
print 'ErrorState...'+ CAST(ERROR_STATE() as varchar)
print 'ErrorProcedure...'+ IsNull(ERROR_PROCEDURE(),'')
print 'ErrorLine...'+ CAST(ERROR_LINE() as varchar)
print 'ErrorMessage...'+ IsNull(ERROR_MESSAGE(),'')
END CATCH
我得到以下结果:
OLE DB提供程序MSDASQL用于链接服务器my_linked_server 返回消息[Informix] [Informix ODBC驱动程序] [Informix]指定的表(xxx)不在数据库中。 。
ErrorNumber ... 7215
ErrorSeverity ... 17
ErrorState ... 1
ErrorProcedure ...
ErrorLine ... 3
ErrorMessage。 ..不能在远程服务器'my_linked_server'上执行语句。
OLE DB provider "MSDASQL" for linked server "my_linked_server" returned message "[Informix][Informix ODBC Driver][Informix]The specified table (xxx) is not in the database.". ErrorNumber...7215 ErrorSeverity...17 ErrorState...1 ErrorProcedure... ErrorLine...3 ErrorMessage...Could not execute statement on remote server 'my_linked_server'.
SQL Server是否存储OLE DB提供程序错误? (捕获这个信息进行调试是有用的。)
Does SQL Server store the OLE DB provider error? (It would be useful to capture this info for debugging.)
推荐答案
我有同样的问题。通过将try catch传递给链接的服务器并使用 OUTPUT
参数返回错误,我发现了如何解决它。例如:
I had this same problem. I found out how to get around it by passing the try catch to the linked server and getting the error back using the OUTPUT
parameter. For example:
SET @command = '
BEGIN TRY
exec (''select * from xxx'')
SELECT @resultOUT = @@ERROR
END TRY
BEGIN CATCH
SELECT @resultOUT = @@ERROR
END CATCH'
SET @ParmDefinition = N'@resultOUT nvarchar(5) OUTPUT'
exec my_linked_server.sp_executesql
@command,
@ParmDefinition,
@resultOUT=@result OUTPUT
这篇关于如何捕获从链接服务器返回的错误消息?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!