> I am hoping that some of you better versed in MS-SQL and CF could clear > this up for me.
> Is there an issue with multiple users accidentally hitting the same data > set that is contained in either a local(#tbl_name) or global(##tbl_name) > temporary table? > Books Online says that a local temp table is local to the current > "session", but how does a session relate to the connections that > CF maintains, ie connection pooling? > If we have, say 5 "select into"s (selecting into a global/local temp > table, one after another in a stored proc, and while user 1 is on the > 3'rd select into, user2 is on the first select into, and both are > "selecting into" the same global/local table, based upon different > where clauses, will this cause "bad" data, ie data that doesn't meet > the where clauses per user, to enter the temp table? And thus the > resulting record set returned would be incorrect? .. Are you using "select into" to actually create the temp table, or are you using "insert into mybable (blah, blah2) select blah, blah2 from myother table"? I'm not sure about CF's connection pooling specifically... If you're using select into to actually create a global temp table and you're concerned about the statements overlapping, I would say yes, this is likely to cause a problem. What I would expect to happen is for one user to begin the process, the stored procedure create the global temp table and before the temp table is eliminated, another user's stored procedure also attempts to create the table and the end result is an error message that says "the table already exists, you big bozo!" ... afaik local temp tables should be fine as long as you don't try to create the same table twice within the same stored procedure call. I could be entirely wrong, but it was my impression from reading the SQL Server docs a while back that they were isolated to the procedure. If you're just using a select statement to insert into an already existing temp table, I would have to see the select into statements and their where clauses to give any specific advice, although I'd suspect you could probably eliminate the possibility of any problems occurring by using locking hints, i.e. INSERT INTO mytemp ( blah, blah2 ) SELECT myot.blah, myot.blah2 FROM myot WITH (NOLOCK) WHERE NOT EXISTS (SELECT blah FROM mytemp WITH (NOLOCK) WHERE blah = myot.blah ) Just as an example, this performs a dirty read of the data with no record locking to determine whether or not to perform the insert, which insulates against having duplication in your temp table. hth > I hope I made sense, let me know if I didn't. So do I. :) Isaac Dealey Certified Advanced ColdFusion Developer www.turnkey.to 954-776-0046 ______________________________________________________________________ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/[email protected]/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
