如何捕获从链接服务器返回的错误消息？

本文介绍了如何捕获从链接服务器返回的错误消息？的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

如何捕获从链接服务器返回的错误消息？

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