Using SQL Server temp tables with ADO.NET Datareader

[damianm]

Hi,

I have a stored procedure which produces the final result set is in a
temporary table (single #) of SQL Server 2000.
I use a DataReader to read the data from this temporary table. Am i
correct in saying the temp table wont go out of scope until I have read
all the records from this datareader?
I understand that a Datareader maintains a connection to the database.
Does this mean the stored procedures is not completed till the
datareader is closed?
This seems to be the case as I get all the records returned as
expected. What confuses me is that temp tables (single #) go out of
scope once the stored procedure is completed.

Anyone who can shed some light on this?

Thanks
Damian

 

[David Browne]

Hi,

I have a stored procedure which produces the final result set is in a
temporary table (single #) of SQL Server 2000.
I use a DataReader to read the data from this temporary table. Am i
correct in saying the temp table wont go out of scope until I have read
all the records from this datareader?
I understand that a Datareader maintains a connection to the database.
Does this mean the stored procedures is not completed till the
datareader is closed?
This seems to be the case as I get all the records returned as
expected. What confuses me is that temp tables (single #) go out of
scope once the stored procedure is completed.

Anyone who can shed some light on this?

The client gets to read the rows from the #temp table before it's destroyed.

David