my life as a tempdb table.... How can I get around this problem?

  • Thread starter Thread starter Russell Smallwood
  • Start date Start date
R

Russell Smallwood

All,

I am writing an ASP.Net wrapper (C#) for a stored procedure library
loosely disguised as an API that uses #mytemptable style tables in the
tempdb to store stuff for a while, until another procedure is called
that uses, then disposes of the #mytemptable table.

This all works great in Query Analyzer but when I call all this stuff
from my ASP.Net code, the temp table dissapears when the datareader I'm
using to create it gets destroyed (there are several steps in each
batch, create the temp table, add some stuff to it, then execute a
function against it, and my temp table is getting destroyed after
step1).

I think I remember reading somewhere that the user temp tables
(#mytemptable) have a pretty limited lifespan. My guess is that they
die when the connection dies, however, they are disapearing when the
SQLDataReader gets detroyed which happens before I close my connection.

As you can see, I'm a bit turned around here, but the bottom line is
that the #mytemptable needs to persist after I've closed or re-assigned
the datareader, as other methods in my class rely on it being there (the
temp table) in order to do their thing.

I can't really change the stored procs so I'm wondering if I can overide
the destructor for the data adapter or something to try and keep it from
covering up its tracks.

I'm worried that I'm going to have to send the whole batch (creation of
the temp table, population of the temp table and execution of the
function against the temp table) over in a stringbuilder nightmare so
the entire batch can execute in one session / connection.

Any help? Clarifications?

-R

--
Russell Smallwood
Relatia Software Corporation

Finally! Time and Billing in GoldMine
www.relatia.net
 
What you appear to be asking is 'how can I get ADO.Net to modify the
internal design of SQL Server?'. Well the answer is, you can't.

You say that you can't really change the stored procedures. It would be
interesting to know why because it is almost certain that it the design of
those stored procedures that is causing you a headache.

User related temporary tables, (those tables with names that start with a
single # character), are destroyed when the 'batch' that created them goes
out of scope. If the table is created within a stored procedure then it is
destroyed when that stored procedure ends.

Database related temporary tables, (those tables with names thate start with
two # characters '##'), can have longer lifetimes depending on what accesses
them.

The subject of temporary tables is quite complex and you will need to read
up on them in the SQL Server Books Online to understand their
idiosynchrises.

If you need the temporary tables to persist until you have finished with
them then you might need to consider standard tables with a suitable naming
convention and then you can issue a 'drop table' at the appropriate time.
 
What was it you said the other day about English being your first language?

'Idiosyncrasies'

not

'idiosynchrises'

HTH
 
I also know how to misspell 'typo'.

One Handed Man said:
What was it you said the other day about English being your first language?

'Idiosyncrasies'

not

'idiosynchrises'

HTH
Click to expand...
 
[This followup was posted to microsoft.public.dotnet.framework.adonet
and a copy was sent to the cited author.]

What you appear to be asking is 'how can I get ADO.Net to modify the
internal design of SQL Server?'. Well the answer is, you can't.

You say that you can't really change the stored procedures. It would be
interesting to know why because it is almost certain that it the design of
those stored procedures that is causing you a headache.

User related temporary tables, (those tables with names that start with a
single # character), are destroyed when the 'batch' that created them goes
out of scope. If the table is created within a stored procedure then it is
destroyed when that stored procedure ends.

Database related temporary tables, (those tables with names thate start with
two # characters '##'), can have longer lifetimes depending on what accesses
them.

The subject of temporary tables is quite complex and you will need to read
up on them in the SQL Server Books Online to understand their
idiosynchrises.

If you need the temporary tables to persist until you have finished with
them then you might need to consider standard tables with a suitable naming
convention and then you can issue a 'drop table' at the appropriate time.
Click to expand...

Thanks Stephany :)

That's exactly what I was hoping to get from my post. You have confirmed
what I thought was happening. Unfortunately, changing the PROCS is not
an option so I'll go another route in my data class.

Thanks again, very helpful

-R
--
Russell Smallwood
Relatia Software Corporation

Finally! Time and Billing in GoldMine
www.relatia.net
 
Back
Top