记录集在存储过程执行后关闭

I'm executing a stored procedure using ADO in VBA. I'm trying to populate the recordset with the results from a stored procedure in SQL Server 2008. Example of the VBA below:

我正在使用VBA中的ADO执行存储过程。我正在尝试使用SQL Server 2008中的存储过程的结果填充记录集。下面的VBA示例：

Public Function DoSomething() As Variant()

Dim oDB As ADODB.Connection: Set oDB = New ADODB.Connection
Dim oCM As ADODB.Command: Set oCM = New ADODB.Command
Dim oRS As ADODB.Recordset

oDB.Open gcConn

With oCM
    .ActiveConnection = oDB
    .CommandType = adCmdStoredProc
    .CommandText = "spTestSomething"
    .NamedParameters = True
    .Parameters.Append .CreateParameter("@Param1", adInteger, adParamInput, , 1)
    Set oRS = .Execute
End With

If Not oRS.BOF And Not oRS.EOF Then 'Error thrown here'
    DoSomething = oRS.GetRows()
Else
    Erase DoSomething
End If

oRS.Close
Set oRS = Nothing
oDB.Close
Set oDB = Nothing

End Function

I am receiving the error Operation is not allowed when the object is closed on the line If Not oRS.BOF... which indicates to me that the stored procedure is not returning a result.

我收到错误当对象在行上关闭时不允许操作如果不是oRS.BOF ...这表明存储过程没有返回结果。

However if I execute the stored procedure in SSMS, it returns a single row. The SP goes along the lines of:

但是，如果我在SSMS中执行存储过程，它将返回单行。 SP遵循以下方针：

CREATE PROC spTestSomething
    @Param1 int
AS
BEGIN

    DECLARE @TempStore table(id int, col1 int);

    INSERT INTO table1
        (param1)
        OUTPUT inserted.id, inserted.col1
        INTO @TempStore
    VALUES
        (@Param1);

    EXEC spOtherSP;

    SELECT
        id,
        col1
    FROM
        @TempStore;
END
GO

The result of executing the procedure in SSMS is:

在SSMS中执行该过程的结果是：

id    col1
__    ____
1     1

Could anyone help with why the recordset is being closed / not filled?

任何人都可以帮助解决记录集关闭/未填写的原因吗？

1 个解决方案

#1

Based on similar question: “Operation is not allowed when the object is closed” when executing stored procedure i recommended in comment:

基于类似的问题：“当关闭对象时不允许操作”执行我在评论中建议的存储过程时：

I suspect 2 reasons: 1) your sp does not contains: SET NOCOUNT ON; and 2) you're working on variable of type: table.

我怀疑有两个原因：1）你的sp不包含：SET NOCOUNT ON; 2）你正在处理类型变量：table。

The most common reason of Operation is not allowed when the object is closed is that that stored procedure does not contain SET NOCOUNT ON command, which prevent extra result sets from interfering with SELECT statements.

当对象关闭时，不允许操作的最常见原因是该存储过程不包含SET NOCOUNT ON命令，这会阻止额外的结果集干扰SELECT语句。

For further information, please see: SET NOCOUNT (Transact-SQL)

有关详细信息，请参阅：SET NOCOUNT（Transact-SQL）

#1