Unanswered: multiple recordsets in nested stored procedures
I have an Access2000 report bound to a stored procedure. Within this stored procedure I create a number of temporay tables, do some calculations and finish with a select statement. When I debug this procedure in Query Analyser, I get the recordset I am looking for. However the Access front end cannot pick up this recordset. The problem appears to be that the insert statments in the procedure are returning temp recordsets that no longet eist once the insert is complete. So the front end thinks there are multiple recordsets, tries to take the first, but it no longer exists. I need to 'wrap' the temp tables part of my stored procedure somehow so that the front end only sees one recordset and that is the select at the bottom. Any ideas out there??
Are you creating more than one temp table in your stored procedure? If so, you may want to explicity do a DROP TABLE #TempTableName for each temp table you create in the stored procedure. Although each temp table for that SPID is implicitly dropped once the sp finishes executing, maybe Access thinks there are multiple recordsets being returned. Although you would think this wouldn't be the case...
If you are joining more than one temp table in the final query's recordset you want Access to use, maybe do an INSERT INTO #FinalTable SELECT ... to put the result set into only one temp table. Then drop everything that's left.
I am using multiple temporary tables. I have found that if I 'SET NOCOUNT ON' at the top of my procedure and only SET NOCOUNT OFF just before I do my final select, only one recordset is returned and my Access front end can pick this up.