How to return SQL Server stored procedure results to Excel

Question

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.

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.

Relevant references:

• ActiveX Data Object Recordset 6.0 Lib
• ActiveX DataObjects 6.1
• ActiveX Remote Data Services 6.0

Answer 1

Add a SET NOCOUNT ON to the beginning of your SQL Stored Procedure.