For quite a while now, I've been using the trick of performing some
fairly complicated processing of records in a stored procedure and saving
the resultant records in a temp table created in the procedure. At the end
of the procedure I do a select * from #temp and open a record set in the
client using this procedure.
This has always worked fine for me up until recently, when I switched
to using the native SQL Server provider (SQLOLEDB) instead of the default
ODBC provider. Now I get an error saying that actions can not be performed
on the object (the recordset) if the object is not open.
Has anyone else encountered this limitation? Is there a magic
combination of property settings to get around it? I'd really like to
continue to use the native provider (progress?!?) but if I can't get around
this limitation I'll have to switch back to ODBC.
I know, I shouldn't be using temp tables if there is a SQL statement
that will do what I want. But in this case, there is no SQL statement to
do the type of sequential and iterative processing that I need done and it's
definitely better performance-wise to process the data on the server instead
of lugging it all down to the client and processing it there.