Friday, February 24, 2012

Global Temporary table disappear

I have a stored procedure creating a global temporary table which is
used later in coldfusion code. Most of time it works. The coldfusion
will output what's in that global temporary table but sometimes that
global temporary table just disappear in coldfusion and the web page
errors out. I checked the procedure and the global temporary table
does get created. If you have any idea about this, please help!!!!!!!!
I'm stuck here and couldn't do anything about it. Thanks a million!Global temp tables disappear when connections stop referencing them. If the
web site has interrupted connectivity (basically every time you close a
connection, load a new page, etc) then they would not persist. If you are
okay with multiple users of the web site potentially accessing the same
global temp table, why not just use a permanent table? At least that will
only get deleted when you want it to...
A
<mirthcyy@.gmail.com> wrote in message
news:1191867216.347474.55650@.50g2000hsm.googlegroups.com...
>I have a stored procedure creating a global temporary table which is
> used later in coldfusion code. Most of time it works. The coldfusion
> will output what's in that global temporary table but sometimes that
> global temporary table just disappear in coldfusion and the web page
> errors out. I checked the procedure and the global temporary table
> does get created. If you have any idea about this, please help!!!!!!!!
> I'm stuck here and couldn't do anything about it. Thanks a million!
>|||Those global temporary table are report tables. Basically if a user
runs a report, the store procedure will do all the calculations and
put the result into a global temporary table. Then the cold fusion
will return the result from that table. All the reports can be
customized so users will pick different columns and apply different
filters so the report tables will all be different. If we are using
permenant tables, we will create a lot if multiple users are using the
same report and they may be a problem for storage issue. As for
tempdb, it will be cleared on regular basis which is more manageable.
I don't understand coldfusion much so I don't know if they are loading
a new page or not. But I will definitely ask some other developer to
check that.

No comments:

Post a Comment