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

Any chance there is no records in table ?

Santosh
– Santosh

2013-10-11 20:25:14 +00:00
Commented Oct 11, 2013 at 20:25 — Santosh
– Santosh, Commented Oct 11, 2013 at 20:25
What's your stored proc?

nutsch
– nutsch

2013-10-11 20:55:24 +00:00
Commented Oct 11, 2013 at 20:55 — nutsch
– nutsch, Commented Oct 11, 2013 at 20:55

RBarryYoung · Accepted Answer · 2013-10-11 20:43:08Z

5

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

answered Oct 11, 2013 at 20:43

RBarryYoung

57k15 gold badges99 silver badges142 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

smoore4 · Accepted Answer · 2013-10-11 20:43:33Z

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.

-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

answered Oct 11, 2013 at 20:43

smoore4

4,9263 gold badges43 silver badges64 bronze badges

Collectives™ on Stack Overflow

How to return SQL Server stored procedure results to Excel

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related