Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
nzmike
Starting Member
21 Posts |
 Posted - 2005-05-02 : 02:58:28
|
| Hi all,I have an asp.Net web page that calls a stored procedure in which I need to create a temporary table (in order to process & summarise the data the way I need it) and my understanding is that a temp table called, say, "#temp_data" is only available to one .net session at once... ie: if multiple users requested the same page would they all get the data created in "#temp_data" fom the first request or would each request get it's own "#temp_data" temp table? I'm pretty sure it's the former so in that case my idea is to pass a GUID into the SP and create the temp table like this:declare @table_name varchar(50)set @table_name = "#temp_" + @GUID --(passed in from .Net)select ...into @table_namewhere ...GOdrop table @table_nameCan I do this? If so, how do I go about it (I just get errors with everything I've tried!). Is there a better way to have a truly temporary table for each and every request froma web page so no user ever sees temporary data from the previous request?Hope someone can point me in the right direction as I'm not sure how best to solve this problem.Mike |
|
|
mfemenel
Professor Frink
1421 Posts |
Posted - 2005-05-02 : 14:33:06
|
| Each request would get it's own temp table. They are not shared. If you and I hit the same page at the same time we'd each have our own version of the table in memory. What exactly are you trying to do? One other option you might consider depending on your needs would be a table variable.(not available in SQL 7). I like using table variables better because they are not stored in temp db, only in memory. Sometimes temp tables bog down if you're doing a lot of transactions with them or you have a lot of users. If you post some more details we can help you narrow in a bit more.Mike"oh, that monkey is going to pay" |
 |
|
|
nzmike
Starting Member
21 Posts |
Posted - 2005-05-02 : 21:12:13
|
| Thanks Mike... I'm pleased to hear each request gets it's own temp table. I wasn't sure because a few years ago I did a big web site for a client using ASP, ADO and Oracle and I remember having this temp table nightmare then - glad to see SQL server doesn't do the same as Oracle and share temp tables!Your idea for "table variables" sounds good but I've not been able to find much on it - do you have a simple example or a link to a good resource for that?Cheers,Mike |
|
|
|
eyechart
Master Smack Fu Yak Hacker
3575 Posts |
Posted - 2005-05-02 : 21:19:25
|
| you can create a global temp table if you want, ##temp_table would be accessable from all sessions.Table variables are useful, especially with small datasets. You can find more information on using them in Books Online (BOL).-ec |
|
|
|
|
|
|
Creating unique temp table with GUID