Do TEMP tables in Stored Procedures not work with ADO Recordsets?

G

Guest

Does anyone know if the use of Temp tables within a SQL Server Stored
Procedure, causes ADO Recordsets to not work? In my Stored Procedure, I am
inserting records into a Temp table, but when I try to create a ADO Recordset
against my stored procedure, I keep getting the error, "Run-time error 3704:
Operation is not allowed when the object is closed". However, if I do not
use the Temp table and just return the records, my ADO recordset works fine.

My 2 stored procedures are below:

*** #1 - This One Does NOT Work (uses Temp Table) ****
CREATE PROCEDURE usp_MyStoredProc
@CustID AS VarChar(10) = NULL,
@ReportType AS VarChar(1) = 'D'
AS
SELECT
Member.CustID,
Member.MemberAutoPK,
Member.CarrierMemID,
MemberPlusID = Member.LastName + ', ' + Member.FirstName + '(' +
Member.CarrierMemID + ')',
MemberLastFirstName = Member.LastName + ', ' + Member.FirstName,
Member.FirstName,
Member.LastName,
Member.DateDispensed
INTO #tmpRecs
FROM Member
WHERE Member.CustID = @CustID

-- Check the Report Type to display Detail records or Summary Records
IF @ReportType = 'D'
BEGIN
SELECT * FROM #tmpRecs ORDER BY CarrierMemID, DateDispensed;
END
ELSE
BEGIN
SELECT 'Report #1' AS Report, COUNT(DISTINCT MemberPlusID) AS NumMembers
FROM #tmpRecs;
END


GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO



*** #2 - This One Works ****
CREATE PROCEDURE usp_MyStoredProc
@CustID AS VarChar(10) = NULL,
@ReportType AS VarChar(1) = 'D'
AS
SELECT
Member.CustID,
Member.MemberAutoPK,
Member.CarrierMemID,
MemberPlusID = Member.LastName + ', ' + Member.FirstName + '(' +
Member.CarrierMemID + ')',
MemberLastFirstName = Member.LastName + ', ' + Member.FirstName,
Member.FirstName,
Member.LastName,
Member.DateDispensed
FROM Member
WHERE Member.CustID = @CustID

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top