从VBA执行SQL Server存储过程并检索所有消息和结果集 [英] Execute SQL Server stored procedure from VBA and retrieve all messages and result sets

问题描述

我希望能够从MS Access VBA执行SQL Server存储过程，这样我可以读取（1）所有结果集，而不仅仅是第一个结果集； （p）

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

1. DAO。使用SQL直通查询，虽然有些笨拙，但我可以在DAO中得到很多想要的东西。它返回3个结果集中的第一个作为记录集，并且通过使用LogMessages属性，我可以获得一个包含发出的消息的表（ Admin – NN）。




2. ADO。使用Connection和Command对象，我可以从存储过程中获得代表第一个结果集的单个记录集。但是，我似乎无法说服它生产仅用于转发的记录集。关于消息，在某一点上，所有消息（至少，我预期的大约150条消息中的前127条）都进入了连接的Errors集合（！），但是当我将消息数量减少到大约90条时，它们都没有出现在我能找到的所有地方。

正如我刚说的那样，我真正想要的是输出从所有结果集中，加上消息。这可能吗？

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

 函数Exec​​uteStoredProcedureADO（SPName作为字符串，Connect作为字符串，ReturnsRecords作为布尔值，_ 
 ParamArray Params（）作为变量）作为ADODB.Recordset 
'v1 .0 2018/06/26 
'在由Connect 
 
 Dim strErr作为字符串
 Dim i作为整数$ b $中的字符串指定的SQL Server数据库上执行存储过程SPName b Dim lngRecs受影响的长度为
 
 Dim cnn作为ADODB.Connection 
 Dim cmd作为ADODB.Command 
 Dim errCurr作为ADODB.Error 
 Dim rst作为ADODB.Recordset 
 
发生错误时转到捕获
设置ExecuteStoredProcedureADO =否
 
设置cnn =新建ADODB.Connection 
 cnn.Errors.Clear 
 cnn.mode = adModeRead 
 cnn.CommandTimeout = 300 
 cnn.Open Connect 
 
设置cmd =新ADODB.Command 
 with cmd 
 .ActiveConnection = cnn 
 .CommandText = SPName 
 .CommandType = adCmdStoredProc 
 
对于i = 0到UBound（Params）步骤4 
 .Parameters.Append .CreateParameter（Params（i），Params（i + 1），adParamInput，Params（i + 2），Params（i + 3））
接下来的i 
 Set rst = New ADODB.Recordset 
 rst.CursorType = adOpenStatic 
如果ReturnRecords然后
'''Set rst = .Execute（lngRecsAffected）
 rst.Open cmd，，adOpenStatic，adLockReadOnly 
其他
设置rst = .Execute（，adExecuteNoRecords）
如果
以
结尾如果返回ReturnRecords则设置ExecuteStoredProcedureADO = rst 
 
最终：
错误如果Len（strErr）>恢复下一个
 0然后调用AppendMsg（strErr）
 Set rst = Nothing 
 Set cmd = Nothing 
 Exit Function 
 
 Catch：
如果cnn.Errors.Count> ; 0然后
与cnn 
在cnn.Errors中的每个errCurr错误
 strErr = strErr& 错误和errCurr.Number& ：& errCurr。说明_ 
& （& errCurr.Source&）& vbCrLf 
下一个errCurr 
 strErr = Left（strErr，Len（strErr）-2）’截断最终CRLF 
以
结尾，否则
 strErr =错误&错误编号和：&错误说明和（& Err.Source&） 
 End if 
 MsgBox strErr，vbOKOnly，gtitle 
 Resume Final 
 
 End Function 
  

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

解决方案

要毫不客气地piggy带@Erik，您想创建一个新类来处理您的处理。 cProcedureHandler 之类的东西。在此类中，您需要使用 WithEvents 关键字声明一个 ADODB.Connection 对象：

  Dim WithEvents cn作为ADODB.Connection 
  

然后，您需要编写一个 InfoMessage 事件处理程序，它将处理多个打印语句。有关 InfoMessage 事件的信息可以找到此处，并使用连接的错误集合可以找到< a href = https://docs.microsoft.com/zh-cn/sql/ado/guide/data/provider-errors?view=sql-server-2017 rel = nofollow noreferrer>此处 。因此，您最终会遇到类似这样的事情：

  Private Sub cn_InfoMessage（ByVal pError如ADODB.Error，adStatus如ADODB。 EventStatusEnum，ByVal pConnection as ADODB.Connection）
 Dim err as ADODB.Error 
 
 Debug.Print cn.Errors.Count& 错误 
 
对于每个错误cn.Errors 
’以您需要的方式处理每个错误/消息。 
调试打印错误描述
下一个错误
结束子
  

由于您已经处理了多个消息，因此现在只需要处理多个记录集，这在您提供的链接中有很好的解释。我注意到的一件事是，示例链接使用 rs没什么来检查是否没有更多的记录集，这对我不起作用。我不得不使用rs State 属性。因此，我最终得到了这一点：

  Public Sub testProcedure（）
 Dim cmd作为ADODB.Command 
 Dim rs作为ADODB.Recordset 
 Dim recordSetIndex作为整数
 
 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 
 
直到rs.State = ObjectStateEnum.adStateClosed 
 Debug.Print rs＃的内容& recordIndex 
直到rs.EOF 
 Debug.Print rs.Fields（0）& rs.Fields（1）
 rs.MoveNext 
循环
 
设置rs = rs.NextRecordset 
 recordSetIndex = recordIndex + 1 
循环
 
 cn.Close 
 Set rs =没什么
 Set cn =没什么
 Set cmd =没什么
 
 End Sub 
  

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

  set obj = new cProcedureHandler 
 obj.testFooProcedure 
  

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

I want to be able to execute a SQL Server stored procedure from MS Access VBA, in such a way that I can read (1) all the resulting result sets, not just the first one; and (2) any messages produced by PRINT statements or similar.

I have a test stored procedure with one input parameter, which produces 3 distinct result sets and about 90 messages. It calls several sub-stored procedures, I can EXEC it perfectly well from SSMS, but it isn’t clear (to me) how best to do it from Access VBA. I have tried the following so far:

1. DAO. Using SQL pass-through queries, I can get a lot of what I want in DAO, though it is a little clunky. It returns the first of the 3 result sets as a recordset, and by using the LogMessages attribute I can get a table ("Admin – NN") containing the emitted messages.

2. ADO. Using Connection and Command objects, I can obtain a single recordset representing the first result set from the stored procedure. However, I can’t seem to persuade it to produce anything but a forward-only recordset. Regarding messages, at one point, all of them (at least, the first 127 of the approx. 150 I expected) were going into the connection’s Errors collection (!), but when I cut the number down to about 90, none of them appeared anywhere at all that I could find.

What I really want, as I said at first, is the output from all result sets, plus the messages. Is this possible?

Here is a listing of the routine I am currently using for executing a stored procedure :

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

Addendum: Regarding the multiple result sets, I am hoping that http://msdn.microsoft.com/en-us/library/ms677569%28VS.85%29.aspx will be of some help.

解决方案

To shamelessly piggy-back off of @Erik, you want to create a new class that will handle your processing. Something like cProcedureHandler. Within this class, you need to declare an ADODB.Connection object using the WithEvents keyword:

Dim WithEvents cn As ADODB.Connection

Then, you need to write a InfoMessage event handler that will take care of the multiple print statements. Information about the InfoMessage event can be found here, and using the connection's Errors collection can be found here. So you'll end up with something like this:

  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

Since you've taken care of the code to handle multiple messages, now you just need to handle the multiple recordsets, which is explained pretty well in the link you provided. One thing I noticed was that the example link used rs is nothing as the check for when there were no more recordsets, which didn't work for me. I had to use the rs State property. So I ended up with this:

  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

Then, when you're ready to run your SP from VBA, just do something like this:

set obj = new cProcedureHandler
obj.testFooProcedure

Another thing (you probably have already done this): Make sure your actual stored procedure in SQL Server sets nocount on.

这篇关于从VBA执行SQL Server存储过程并检索所有消息和结果集的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！