ADO Recordsets based on Local Temp Tables

ADO Recordsets based on Local Temp Tables

Post by BurtonRobert » Sat, 12 May 2001 05:17:22



If I try to create a recordset in VB or Access based on the stored procedure
"procTempTableExample" (below) and then write the fields to the Immediate
window with a debug.print I get the following error message :
"3704: Operation is not allowed when object is closed".  However, if I use
"procFunctionExample" (below) which calls the UDF "fn_Example" (below) to do
the same thing, the operation succeeds. Can anyone explain why this is? And
under what circumstances I can create and use an ADO recordset based on
local temporary table data?  I can't find anything in the KB.

CREATE  Procedure procTempTableExample
AS
CREATE Table #Temp (Col1 int, Col2 int)
INSERT #Temp (Col1, Col2)
VALUES (1,2)
INSERT #Temp (Col1, Col2)
VALUES (3,4)
SELECT Col1, Col2 FROM #Temp
RETURN

CREATE  Procedure procFunctionExample
AS
SELECT * FROM dbo.fn_Example(0)
RETURN



AS
BEGIN

VALUES (1,2)

VALUES (3,4)
RETURN
END

 
 
 

ADO Recordsets based on Local Temp Tables

Post by Tibor Karasz » Sat, 12 May 2001 05:47:50


Try adding SET NOCOUNT ON in the beginning of the proc code.

--
Tibor Karaszi, SQL Server MVP
FAQ from Neil at: http://www.sqlserverfaq.com
Please reply to the newsgroup only, not by email.


> If I try to create a recordset in VB or Access based on the stored
procedure
> "procTempTableExample" (below) and then write the fields to the Immediate
> window with a debug.print I get the following error message :
> "3704: Operation is not allowed when object is closed".  However, if I use
> "procFunctionExample" (below) which calls the UDF "fn_Example" (below) to
do
> the same thing, the operation succeeds. Can anyone explain why this is?
And
> under what circumstances I can create and use an ADO recordset based on
> local temporary table data?  I can't find anything in the KB.

> CREATE  Procedure procTempTableExample
> AS
> CREATE Table #Temp (Col1 int, Col2 int)
> INSERT #Temp (Col1, Col2)
> VALUES (1,2)
> INSERT #Temp (Col1, Col2)
> VALUES (3,4)
> SELECT Col1, Col2 FROM #Temp
> RETURN

> CREATE  Procedure procFunctionExample
> AS
> SELECT * FROM dbo.fn_Example(0)
> RETURN



> AS
> BEGIN

> VALUES (1,2)

> VALUES (3,4)
> RETURN
> END


 
 
 

ADO Recordsets based on Local Temp Tables

Post by Shen » Sat, 12 May 2001 13:27:56


Add one line to procTempTableExample after AS:

SET NOCOUNTS ON

ShenC


> If I try to create a recordset in VB or Access based on the stored
procedure
> "procTempTableExample" (below) and then write the fields to the Immediate
> window with a debug.print I get the following error message :
> "3704: Operation is not allowed when object is closed".  However, if I use
> "procFunctionExample" (below) which calls the UDF "fn_Example" (below) to
do
> the same thing, the operation succeeds. Can anyone explain why this is?
And
> under what circumstances I can create and use an ADO recordset based on
> local temporary table data?  I can't find anything in the KB.

> CREATE  Procedure procTempTableExample
> AS
> CREATE Table #Temp (Col1 int, Col2 int)
> INSERT #Temp (Col1, Col2)
> VALUES (1,2)
> INSERT #Temp (Col1, Col2)
> VALUES (3,4)
> SELECT Col1, Col2 FROM #Temp
> RETURN

> CREATE  Procedure procFunctionExample
> AS
> SELECT * FROM dbo.fn_Example(0)
> RETURN



> AS
> BEGIN

> VALUES (1,2)

> VALUES (3,4)
> RETURN
> END

 
 
 

ADO Recordsets based on Local Temp Tables

Post by BurtonRobert » Sun, 13 May 2001 01:46:25


Thanks very much! It worked
Burton Roberts


> Add one line to procTempTableExample after AS:

> SET NOCOUNTS ON

> ShenC



> > If I try to create a recordset in VB or Access based on the stored
> procedure
> > "procTempTableExample" (below) and then write the fields to the
Immediate
> > window with a debug.print I get the following error message :
> > "3704: Operation is not allowed when object is closed".  However, if I
use
> > "procFunctionExample" (below) which calls the UDF "fn_Example" (below)
to
> do
> > the same thing, the operation succeeds. Can anyone explain why this is?
> And
> > under what circumstances I can create and use an ADO recordset based on
> > local temporary table data?  I can't find anything in the KB.

> > CREATE  Procedure procTempTableExample
> > AS
> > CREATE Table #Temp (Col1 int, Col2 int)
> > INSERT #Temp (Col1, Col2)
> > VALUES (1,2)
> > INSERT #Temp (Col1, Col2)
> > VALUES (3,4)
> > SELECT Col1, Col2 FROM #Temp
> > RETURN

> > CREATE  Procedure procFunctionExample
> > AS
> > SELECT * FROM dbo.fn_Example(0)
> > RETURN



> > AS
> > BEGIN

> > VALUES (1,2)

> > VALUES (3,4)
> > RETURN
> > END

 
 
 

1. ADO copies the whole table to Local Settings\Temp

Every time a record set is open, ADO makes a copy of that table/query in
Local Settings\Temp directory. And then deletes it again.

Is there a way to disable this annoying feature?

It is anoying, couse you have to wait till every table is copied. And those
tables are in GIGABYTE sizes each.

Is there a way to disable this annoying feature?

2. How to get latest Patchess aand SP Details by mail

3. Problem with retrieving an ADO Recordset from SQLServer using Temp Tables

4. SQLJ question - ConnectionContext and DefaultContext

5. Returning ADO Recordset from SP Temp table

6. Monthly Posting: IUG-Talk Mailing List

7. Problem with retrieving an ADO Recordset from SQLServer using Temp Tables

8. HELP: temp table in procedure, and ADO recordset

9. #Temp table as ADO Recordset

10. Problem with retrieving an ADO Recordset from SQLServer using Temp Tables

11. ADO Returning Recordset from Temp table in Stored Proc