从 VBA 执行 SQL Server 存储过程并检索所有消息和结果集

我希望能够从 MS Access VBA 执行 SQL Server 存储过程，这样我就可以读取 (1) 所有结果结果集，而不仅仅是第一个结果集； (2) 由 PRINT 语句或类似语句产生的任何消息。

我有一个带有一个输入参数的测试存储过程，它会生成 3 个不同的结果集和大约 90 条消息。它调用多个子存储过程，我可以从 SSMS 完美地执行它，但（对我来说）不清楚如何最好地从 Access VBA 执行它。到目前为止我已经尝试过以下操作：

1. 道。使用 SQL 传递查询，我可以在 DAO 中获得很多我想要的东西，尽管它有点笨拙。它返回 3 个结果集中的第一个作为记录集，通过使用 LogMessages 属性，我可以获得一个包含发出的消息的表（“Admin – NN”）。

2. 阿多。使用 Connection 和 Command 对象，我可以获得表示存储过程中第一个结果集的单个记录集。然而，我似乎无法说服它产生除了只进记录集之外的任何东西。关于消息，在某一时刻，所有消息（至少是我预期的大约 150 条中的前 127 条）都将进入连接的错误集合 (!)，但是当我将数量减少到大约 90 条时，没有一条消息出现在我能找到的任何地方。

正如我一开始所说，我真正想要的是输出所有结果集, 加上消息。这可能吗？

以下是我当前用于执行存储过程的例程的列表：

Function ExecuteStoredProcedureADO(SPName As String, Connect As String, ReturnsRecords As Boolean, _
   ParamArray Params() As Variant) As ADODB.Recordset
   ' v1.0 2018/06/26
   ' execute stored procedure SPName on a SQL Server database specified by the string in Connect

   Dim strErr As String
   Dim i As Integer
   Dim lngRecsAffected As Long

   Dim cnn As ADODB.Connection
   Dim cmd As ADODB.Command
   Dim errCurr As ADODB.Error
   Dim rst As ADODB.Recordset

   On Error GoTo Catch
   Set ExecuteStoredProcedureADO = Nothing

   Set cnn = New ADODB.Connection
   cnn.Errors.Clear
   cnn.mode = adModeRead
   cnn.CommandTimeout = 300
   cnn.Open Connect

   Set cmd = New ADODB.Command
   With cmd
      .ActiveConnection = cnn
      .CommandText = SPName
      .CommandType = adCmdStoredProc

      For i = 0 To UBound(Params) Step 4
         .Parameters.Append .CreateParameter(Params(i), Params(i + 1), adParamInput, Params(i + 2), Params(i + 3))
      Next i
      Set rst = New ADODB.Recordset
      rst.CursorType = adOpenStatic
      If ReturnsRecords Then
         '''Set rst = .Execute(lngRecsAffected)
         rst.Open cmd, , adOpenStatic, adLockReadOnly
      Else
         Set rst = .Execute(, , adExecuteNoRecords)
      End If
   End With
   If ReturnsRecords Then Set ExecuteStoredProcedureADO = rst

Final:
   On Error Resume Next
   If Len(strErr) > 0 Then Call AppendMsg(strErr)
   Set rst = Nothing
   Set cmd = Nothing
   Exit Function

Catch:
   If cnn.Errors.Count > 0 Then
      With cnn
         For Each errCurr In cnn.Errors
            strErr = strErr & "Error " & errCurr.Number & ": " & errCurr.Description _
               & " (" & errCurr.Source & ")" & vbCrLf
         Next errCurr
         strErr = Left(strErr, Len(strErr) - 2) ' truncate final CRLF
      End With
   Else
      strErr = "Error " & Err.Number & ": " & Err.Description & " (" & Err.Source & ")"
   End If
   MsgBox strErr, vbOKOnly, gtitle
   Resume Final

End Function

附录：关于多个结果集，我希望http://msdn.microsoft.com/en-us/library/ms677569%28VS.85%29.aspx会有一定的帮助。

为了无耻地利用@Erik，你想创建一个新的类来处理你的处理。就像是cProcedureHandler。在这个类中，您需要声明一个ADODB.Connection对象使用WithEvents关键词：

Dim WithEvents cn As ADODB.Connection

然后，你需要写一个InfoMessage事件处理程序将处理多个打印语句。有关信息InfoMessage可以找到事件here，并使用连接的Errors可以找到合集here。所以你最终会得到这样的结果：

  Private Sub cn_InfoMessage(ByVal pError As ADODB.Error, adStatus As ADODB.EventStatusEnum, ByVal pConnection As ADODB.Connection)
     Dim err As ADODB.Error

     Debug.Print cn.Errors.Count & " errors"

     For Each err In cn.Errors
        ' handle each error/message the way you need to.
        Debug.Print err.Description
     Next err
  End Sub

由于您已经处理了处理多条消息的代码，现在您只需要处理多个记录集，这在您提供的链接中得到了很好的解释。我注意到的一件事是示例链接使用rs is nothing作为检查何时没有更多记录集，这对我不起作用。我不得不使用 rsState财产。所以我最终得到了这个：

  Public Sub testProcedure()
     Dim cmd As ADODB.Command
     Dim rs As ADODB.Recordset
     Dim recordSetIndex As Integer

     Set cn = modData.getConnection

     Set cmd = New ADODB.Command
     cmd.ActiveConnection = cn
     cmd.CommandType = adCmdStoredProc
     cmd.CommandText = "dbo.sp_foo"

     Set rs = cmd.Execute

     recordSetIndex = 1

     Do Until rs.State = ObjectStateEnum.adStateClosed
        Debug.Print "contents of rs #" & recordIndex
        Do Until rs.EOF
           Debug.Print rs.Fields(0) & rs.Fields(1)
           rs.MoveNext
        Loop

        Set rs = rs.NextRecordset
        recordSetIndex = recordIndex + 1
     Loop

     cn.Close
     Set rs = Nothing
     Set cn = Nothing
     Set cmd = Nothing

  End Sub

然后，当您准备好从 VBA 运行 SP 时，只需执行以下操作：

set obj = new cProcedureHandler
obj.testFooProcedure

另一件事（您可能已经这样做了）：确保 SQL Server 中的实际存储过程设置了 nocount。