ADO, Stored Proc, and temp tables

[JCARPENTER]

I have a couple of stored procedures which use
temporary tables or table variables. I want
to access a returned record set using ADO (VC++ vein).
I can get back recordsets from SP's that don't use
temp tables and from SP's that have temp tables as
long as I don't insert any rows into them. But
if I actually use the tables, the command object's
Excute method returns a recordset that I can't access.
Any attempt to call a method (even checking for EOF)
throws a _com_error exception whose Description is
"Operation is not allowed when the object is closed."
The call works if I do a select from the temp table
in the proc without actually inserting any rows.
This is just too weird. Can someone help?

Thanks,
Jim Carpenter

 

[tlbroadbent]

Do you have SET NOCOUNT ON at the beginning of your SP? That option will inhibit the row count messages that occur when records are selected or manipulated in T-SQL. Your procedure should then return the proper result set. Add SET NOCOUNT OFF at the end of the SP. Terry L. Broadbent
Programming and Computing Resources

http://tlbroadbent.home.attbi.com/prog.htm

 

[JCARPENTER]

That did it! Thanks! It also "fixed"
a couple of other problems.

Jim