Stored Procedure with #Temp table question 1

[behbeh]

I am developing a VB6 application with a SQL Server 7 backend. I have a stored procedure that creates a local temporary table to insert records to be queried from the front-end.

My VB code uses the ADO Command object to run this stored procedure and I am using
'Set rsObject=cmdObject.Execute'
so I can loop through the records of the temp table. However, I am getting a runtime error #3704 when I try to reference the recordset after the above code (can't reference a closed object)and I know there should be records.

When I execute the stored procedure in SQL Analyzer, I get all the appropriate records from this temp table.

I just want to know if anyone has ever tried retrieving records in a local temp table (local to the stored procedure) from a front-end application, and if so, what's the best way to do it using a parameter stored procedure.

Thanks!

 

[tlbroadbent]

Try adding SET NOCOUNT ON at the begining of the stored procedure.

Check the following article.

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsqlsg/html/msdn_adosql.asp

Terry Broadbent
Please review faq183-874.

"The greatest obstacle to discovery is not ignorance -- it is the illusion of knowledge." - Daniel J Boorstin