I can verify that my VBA code is executing the stored procedure at the server but I'm unable to get the recordset back into Excel.
我可以验证我的VBA代码正在服务器上执行存储过程，但我无法将记录集返回到Excel。
Set con = New ADODB.Connection
Set cmd = New ADODB.Command
Set rs = New ADODB.Recordset
con.Open "Provider=SQLOLEDB;Data Source=" & ServerName & ";...."//works
set rs = cmd.Execute(, SP_Param, adCmdStoredProc) // executes
If rs.EOF = False Then WSP1.Cells(4, 1).CopyFromRecordset rs
//tosses Operation is not allowed when the object is closed.
I've tried using the SQLOLEDB provider but am unable to get through to the stored procedure using that.
我已经尝试使用SQLOLEDB提供程序，但无法使用它来访问存储过程。
Relevant references:
相关参考文献：
- ActiveX Data Object Recordset 6.0 Lib
- ActiveX数据对象Recordset 6.0 Lib
- ActiveX DataObjects 6.1
- ActiveX DataObjects 6.1
- ActiveX Remote Data Services 6.0
- ActiveX远程数据服务6.0
2 个解决方案
#1
5
Add a SET NOCOUNT ON to the beginning of your SQL Stored Procedure.
将SET NOCOUNT ON添加到SQL存储过程的开头。
#2
2
I doubt this is the problem but you should try setting NOCOUNT ON in the proc to make sure that is not the problem.
我怀疑这是问题，但你应该尝试在proc中设置NOCOUNT，以确保不是问题。
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
#1
5
Add a SET NOCOUNT ON to the beginning of your SQL Stored Procedure.
将SET NOCOUNT ON添加到SQL存储过程的开头。
#2
2
I doubt this is the problem but you should try setting NOCOUNT ON in the proc to make sure that is not the problem.
我怀疑这是问题，但你应该尝试在proc中设置NOCOUNT，以确保不是问题。
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;