calling a store procedure (with an INSERT statement) from ADO in results in closed recordset
I have a stored procedure that contains an INSERT statement (I am inserting into temporary a table that forms part of the result of the stored proc). When calling this stored proc from C++/ADO, I get back a closed record set when running in VS2005/Windows XP x64 (this has previously worked on VS2003/WindowsXP).
The stored proc below is a contrived example for demonstration of the problem.
CREATE PROCEDURE TestStoredProc @Param1 varchar(32), @Param2 int
AS
INSERT into MyTable (Col1, Col2) values (@Param1, @Param2)
select * from MyTable
Note that when the INSERT is removed from the stored proc, I get back an open recordset containing data as expected.
Please help!
Steve
Hi
This is because the SP you write returns 2 result sets on for the insert statement and the other for the Select statement
you can set NO_COUNT on and this will work (Set NO_COUNT ON) at the start of the SP and (Set NO_COUNT ON) before the end
or you can navigate to the next recordset object using the NextRecordset method of the recordset object
