gpt4 book ai didi

sql-server - 如何捕获从链接服务器返回的错误消息?

转载 作者:行者123 更新时间:2023-12-03 08:02:29 24 4
gpt4 key购买 nike

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

例如,如果我在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 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提供程序错误? (捕获此信息以进行调试将很有用。)

最佳答案

我有同样的问题。我通过将try catch传递到链接服务器并使用OUTPUT参数找回了错误来找到解决方法。例如:

    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

关于sql-server - 如何捕获从链接服务器返回的错误消息?,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/2168207/

24 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com