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 |
|
real_pearl
Posting Yak Master
106 Posts |
 Posted - 2004-08-11 : 05:52:56
|
| If I create a temporary table like thisEXEC('select * into #tmpA from tblA')When I query this table, I got an errorServer: Msg 208, Level 16, State 1, Line 1Invalid object name '#lot'.but when I use ##, I can query the table after execute stmt. But in a client server environment would it be right to use ## with temporary table because some queries might execute in 2 minutes due to large data. in this case what will happen if one user runs the query and immediately other person executes the same query i mean queries in a stored procedure. Whose ##tmpA table will be existing? either first users table will exists or seconds or some error will occur for the second user. I dont want any error, just want result saved for each user. Please tell me how to cater this issue? |
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2004-08-11 : 06:21:37
|
| a global temp (##temp) will persist over multiple sessions. try opening 2 windows in QA and try select * into ##temp from MyTable select * ##temp in one and select * ##temp in other. the data will be in both sessions. Go with the flow & have fun! Else fight the flow :) |
 |
|
|
real_pearl
Posting Yak Master
106 Posts |
Posted - 2004-08-11 : 06:38:08
|
| I opened 2 instances of Query Analyzer and INSTANCE 1:EXEC('select * into ##temp from MyTable where Code = 155')select * from ##tempINSTANCE 2:EXEC('select * into ##temp from MyTable where Code = 156')select * from ##tempand firstly excuted the command in Instance 1 and then in 2nd, but in 2nd got errorServer: Msg 2714, Level 16, State 6, Line 1There is already an object named '##temp' in the database. |
|
|
|
ditch
Master Smack Fu Yak Hacker
1466 Posts |
Posted - 2004-08-11 : 07:25:21
|
| As Spirit said - the ##tables span across sessions which means that if you try to create a ##Table that already exists from another session then you will get that error.You wont get that error if you created 2 #tables from different sessions.Duane. |
|
|
|
real_pearl
Posting Yak Master
106 Posts |
Posted - 2004-08-11 : 07:44:24
|
| But how can I create and access a #temp table using EXEC() stmt. Because my stmt is dynamic that is y I have to use EXEC() command for the execution of the stmt. while I want to use that temp table ouside. Please let me know how to cater this issue in a client/server environment.Thanks |
|
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2004-08-11 : 07:48:02
|
| you could maybe put it in a stored procedure, so you won't need a temp table.you could name every global (##temp) temp table differently....when a ##temp is created, another user can't create a temp table with the same name.different windows in QA can represent different users, so you can play with that.Go with the flow & have fun! Else fight the flow :) |
|
|
|
real_pearl
Posting Yak Master
106 Posts |
Posted - 2004-08-11 : 07:51:59
|
| What I was thinking to create the temp table on the name of the user's id. But I was looking for a simpler approach.ANyways thanks Spirit. |
|
|
|
|
|
|
|
|
Temporary Tables