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'"

ktharsis · Accepted Answer · 2010-08-23 14:04:06Z

3

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

answered Aug 23, 2010 at 14:04

ktharsis

3,1901 gold badge21 silver badges30 bronze badges

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

Collectives™ on Stack Overflow

Excel 2003 VBA fails to call SQL sproc with temporary tables

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related