时间戳

首页 > 解决方案 > 从 VBA 执行 SQL Server 存储过程并检索所有消息和结果集

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

问题描述

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

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

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

  2. ADO。使用 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 会有所帮助。

标签： sql-serverms-accessvbaado

解决方案

为了无耻地背负@Erik，您想要创建一个新类来处理您的处理。类似的东西cProcedureHandler。在此类中，您需要使用关键字声明一个ADODB.Connection对象：WithEvents

Dim WithEvents cn As ADODB.Connection

然后，您需要编写一个InfoMessage事件处理程序来处理多个打印语句。有关InfoMessage事件的信息可以在这里找到，使用连接的Errors集合可以在这里找到。所以你最终会得到这样的东西：

  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 on。

推荐阅读