Recordset Closed After Stored Procedure Execution
ado, recordset, sql-server, stored-procedures, vba
Solution
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)
Problem
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?