Recordset Closed After Stored Procedure Execution

Question

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:

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.

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

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:

id    col1
__    ____
1     1

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

Whipstitch

TO identify how many records have been affected by SP, use Exceute method together with params: msdn.microsoft.com/en-us/library/windows/desktop/… instead of BOF and EOF – Tremble

@MaciejLos Thanks for your response. There's multiple rows affected as there's several SP's being executed. Are you suggesting to check this prior to trying to work with the recordset? Thanks – Whipstitch

Yeah. In that case, it won't be needed to check values returned by BOF and EOF methods. If RecordsAffected param returns value bigger than zero, then recordset is filled with values ;) – Tremble

@MaciejLos For the most part, records affeceted will return -1 in MS Access. – Shove

@MaciejLos Thanks for the suggestion. RecordsAffected returns 1. Which is confusing... – Whipstitch

@Gareth, i'll try to re reconstruct your issue. Please, be patience and wait for my response. – Tremble

I'm working on real table, instead of table variable and it works like a charm. I suspect 2 reasons: 1) your sp does not contains: SET NOCOUNT ON; and you're working on variable of type table. What's your connection string? Check it. In my case both BOF and EOF return false. – Tremble

Spot on with SET NOCOUNT ON thanks. Stick it in an answer and I'll accept. Thanks again – Whipstitch

Answer 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.

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.

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

Tremble