Records are closed after the stored procedure

Question

I am executing a stored procedure using ADO in VBA. I am trying to populate a recordset with results from a stored procedure in SQL Server 2008. The VBA example 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 get an Operation is not allowed when the object is closed error in the If Not oRS.BOF... , which tells me that the stored procedure does not return a result.

However, if I execute a stored procedure in SSMS, it returns a single row. SP goes line by line:

 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 the procedure in SSMS is:

 id col1 __ ____ 1 1 

Can anyone help on why the record set is being closed / not populated?

Answer 1

Based on a similar question: “The operation is not allowed when the object is closed” when executing the stored procedure , I recommend in the comment:

I suspect 2 reasons: 1) your sp does not contain: SET NOCOUNT ON; and 2) you are working on a variable like: table.

The most common reason Operation is not allowed when the object is closed is because this stored procedure does not contain the SET NOCOUNT ON , which prevents additional result sets from interfering with SELECT .

See SET NOCOUNT (Transact-SQL) for more information.