如何处理sp_executesql执行错误并传递至Microsoft Access？

我通过存储过程生成SQL语句，再用sp_executesql执行该语句创建指向链接服务器的视图。在SSMS中执行这个视图时会抛出错误：

Msg 7321, Level 16, State 2, Procedure LINKEDSERVERVIEW, Line 3 [Batch Start Line 8]
An error occurred while preparing the query "SELECT..."

这个错误是预期的，但问题在于调用程序Microsoft Access接收不到这个错误，存储过程执行时看起来没有异常。请问如何读取sp_executesql的执行结果并自行处理错误？

存储过程代码：

declare @Status = 'OK'
declare @def nvarchar(4000) = 'ALTER VIEW dbo.LINKEDSERVERVIEW AS  SELECT * FROM OPENQUERY(LINKEDSERVER2, ''SELECT * FROM COM_INTEGRATION_SHIP'') AS derivedtbl_1  '
begin try
begin tran
  exec sp_executesql @def -- 在SSMS中报错，但Access中无反应
commit tran
end try
begin catch
  set @Status = (select error_message())
end catch

VBA代码：

Function SwitchLinkedServer(strDSN As String) As Boolean
On Error GoTo ErrLabel

   Dim strTo As String, con As ADODB.Connection, cmd As New ADODB.Command, prm As New ADODB.Parameter

   SwitchLinkedServer = False

   ' 将DSN转换为存储过程用的链接服务器名
   strTo = DLookup("LinkedServername", "[#DSN2LinkedServer]", "DSNname = '" & strDSN & "'")
   
   If Not EstablishConnection(con) Then
      ErrHandler glbLastErrNum, glbLastError
      GoTo ExitLabel
   End If

   Set cmd.ActiveConnection = con
   cmd.CommandType = adCmdStoredProc
   cmd.CommandText = "_SwitchLinkedServer"
   
   Set prm = cmd.CreateParameter("@To", adVarChar, adParamInput, 20, strTo)
   cmd.Parameters.Append prm
   Set prm = cmd.CreateParameter("@Status", adVarChar, adParamOutput, 255)
   cmd.Parameters.Append prm

   cmd.Execute
 
   If cmd.Parameters("@Status") <> "OK" Then
      MsgBox "ERROR"
      GoTo ExitLabel
   End If

   SwitchLinkedServer = True

ExitLabel:
On Error Resume Next

   Set prm = Nothing
   Set cmd = Nothing
   TerminateConnection con
   Exit Function

ErrLabel:

   ErrHandler Err.Number, Err.Description
   Resume ExitLabel
   
End Function

1. 修正存储过程的错误捕获逻辑 #

当前存储过程没报错，是因为ALTER VIEW执行时不会验证链接服务器的可用性——视图的查询验证是在首次调用时才触发的。要提前捕获错误，需在修改视图前先测试链接服务器：

CREATE PROCEDURE _SwitchLinkedServer
    @To nvarchar(20),
    @Status nvarchar(255) OUTPUT
AS
BEGIN
    SET @Status = 'OK';
    DECLARE @def nvarchar(4000);
    SET @def = N'ALTER VIEW dbo.LINKEDSERVERVIEW AS SELECT * FROM OPENQUERY(' + QUOTENAME(@To) + ', ''SELECT * FROM COM_INTEGRATION_SHIP'') AS derivedtbl_1';

    BEGIN TRY
        BEGIN TRAN;

        -- 先测试链接服务器是否可达
        DECLARE @testSql nvarchar(100);
        SET @testSql = N'EXEC(''SELECT 1'') AT ' + QUOTENAME(@To);
        EXEC sp_executesql @testSql;

        -- 测试通过后再修改视图
        EXEC sp_executesql @def;

        COMMIT TRAN;
    END TRY
    BEGIN CATCH
        IF @@TRANCOUNT > 0
            ROLLBACK TRAN;
        SET @Status = ERROR_MESSAGE();
    END CATCH
END

• 补充了链接服务器预测试，确保在修改视图前就能发现连接问题
• 修正原存储过程的语法错误（@Status未指定数据类型）
• 确保事务在出错时回滚

2. 确保Access正确获取输出参数 #

当前VBA代码逻辑正确，但需注意：

• 存储过程的@Status必须显式定义为OUTPUT参数（上面的修正版已添加）
• 可添加调试代码确认参数返回值：

cmd.Execute
Debug.Print "返回状态: " & cmd.Parameters("@Status").Value ' 调试用

• 若参数仍无法返回，检查ADODB连接的CursorLocation是否设置为adUseClient

3. 可选：创建视图后立即验证可用性 #

如果需要确保视图创建后能正常查询，可在ALTER VIEW后追加测试查询：

-- 在TRY块的COMMIT前添加
SELECT TOP 1 * FROM dbo.LINKEDSERVERVIEW;

这样视图查询的错误也会被CATCH块捕获，确保存储过程返回错误状态。

内容的提问来源于stack exchange，提问作者whatwhatwhat