vba 存储过程执行后记录集关闭
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/28677262/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Recordset Closed After Stored Procedure Execution
提问by Gareth
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 closedon the line If Not oRS.BOF...which indicates to me that the stored procedure is not returning a result.
我Operation is not allowed when the object is closed在行上收到错误，If Not 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?
任何人都可以帮助解释为什么记录集被关闭/未填充？
回答by Maciej Los
Based on similar question: “Operation is not allowed when the object is closed” when executing stored procedurei 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）您正在处理类型为：表的变量。
The most common reason of Operation is not allowed when the object is closedis that that stored procedure does not contain SET NOCOUNT ONcommand, which prevent extra result sets from interfering with SELECTstatements.
最常见的原因Operation is not allowed when the object is closed是该存储过程不包含SET NOCOUNT ON命令，这可以防止额外的结果集干扰SELECT语句。
For further information, please see: SET NOCOUNT (Transact-SQL)
有关更多信息，请参阅：SET NOCOUNT (Transact-SQL)
