Excel 2003 VBA fails to call SQL sproc with temporary tables

Question

I am trying to get resultset from SQL 2008 sproc into Excel 2003 using VBA.

It worked for few sprocs but when I tried the one which uses temp table or table variable VBA fails with err 3704 "Operation is not allowed when the object is closed" on the following line:

Sheet1.Range("A2").CopyFromRecordset rsMyDB

If I comment out select into the temp table / table variable the very same VBA works just fine.

The ADO I reference in the VBA module - "MS ActiveX Data Objects 2.8 Library"

The SQL as follows:

.Open "EXEC SprocWithTempTable '20100810', '20100811'"

Answer 1

Add SET NOCOUNT ON at the beginning of the SQL proc and it should get through.