It's entirely possible for a CF page to change connections in the middle of processing a request, and therefore lose sight of the temp table. It's also possible for the new connection to be one that was previously used by another invocation of the same page, producing the problem that James mentions.
You can avoid both of these problems by wrapping the relevant code in a <cftransaction> - the transaction binds the connection to the CF thread - but, like all locking, you have to consider the impact on throughput. Jaime Metcher > -----Original Message----- > From: James Smith [mailto:[EMAIL PROTECTED] > Sent: Thursday, 17 April 2008 10:38 PM > To: CF-Talk > Subject: Re: CF and SQL Server temporary tables > > > If I remember correctly if you have CF maintaining connections your > temp table will hang around almost indefinitely, best practice would > be to DROP it once you are done with it. > > Also worth noting that if the page that creates the table can be > called multiple times simultaneously (by different users of the system > for example) then the second request will fail and error on you > because the table has already been created. > > If you need a truly temporary table you could lock the database (to > prevent other requests getting in the way) create the table, do what > you need it for, then drop the table and unlock the database. If the > table will hold data needed by multiple requests I would look at some > way of creating a permanent table and managing the data across > requests. > > -- > > Jay > > On Thu, Apr 17, 2008 at 1:13 PM, Rick Root > <[EMAIL PROTECTED]> wrote: > > so I have a question about temporary tables in SQL Server 2005. > > > > From the SQL Server documentation for CREATE TABLE: > > > > You can create local and global temporary tables. Local > temporary tables are > > visible only in the current session, and global temporary > tables are visible > > to all sessions. Temporary tables cannot be partitioned. > > > > Prefix local temporary table names with single number sign > (#*table_name*), > > and prefix global temporary table names with a double number sign (##* > > table_name*). > > What exactly does "current session" mean as it applies to a Coldfusion > > request? The life of the request? the life of the > transaction, if any? > > The life of the executing query? Or, since CF maintains the database > > connection between requests, and pools connections, how does > that affect > > things? > > > > I'm hoping that each cf request is a separate "session" as far > as SQL Server > > is concerned. > > > > Thanks! > > > > Rick > > > > > > -- > > Rick Root > > New Brian Vander Ark Album, songs in the music player and cool > behind the > > scenes video at www.myspace.com/brianvanderark > > > > > > > > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;192386516;25150098;k Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:303698 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
