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 |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2002-10-25 : 09:40:56
|
| desrie writes "I CAN USE ADO FIND IF THE TEMPORARY TABLE IS EXIST OR NOT.THE FOLLOWING CODES DOESN'T WORK.'''My Codes''''PRIVATE MYSUB() DIM RSTMP AS NEW ADODB.RECORDSET DIM TmpName AS STRING RSTMP.USERLOCATION =3 RSTMP.OPEN "SELECT NAME FROM TEMPDB..SYSOBJECTS " _ & " WHERE NAME = '#MYTMPTABLE'AND TYPE='U'",CONNECTION,1,1 IF NOT RSTMP.EOF THEN CONNECTION.EXECUTE "DROP TABLE #MYTMPTABLE" END IF RSTMP.CLOSEEND MYSUBBUT THE "RSTMP.EOF" ALWAYS RETURN "TRUE". I CAN USE "SELECT * FROM #MYTMPTABLE" AND IT RETURN RECORDS.BUT WHERE IS THE TEMPORARY TABLE,I NEED YOUR HELP!!PLEASE ANSWER ME ASAP!!!! THANK YOU!!!" |
|
|
lozitskiy
Starting Member
28 Posts |
Posted - 2002-10-25 : 11:36:14
|
| First of all name of temporary table in tempdb..sysobjects looks like:#MYTMPTABLE_____________________________122You can try to use LIKE but it needs to be careful...RSTMP.OPEN "SELECT NAME FROM TEMPDB..SYSOBJECTS " _ & " WHERE NAME like '#MYTMPTABLE%'AND TYPE='U'",-------------MCP MSSQL |
 |
|
|
M.E.
Aged Yak Warrior
539 Posts |
Posted - 2002-10-25 : 11:54:05
|
| Why do people insist on looking into databases that they shouldn't be? Which database did you create the temp table in? Drop it there. Curious... Did you know that a temp table is only there for a single instance? You could have the same temp table named #mytemptable created 50 times in a database, each one being used/opened in a different instance. Do not drop from the temp database. Lets say you have multiple users on a web page. What happens when one of them creates a temp table about 2 secs before a delete on all temp tables in the tempdb is run by another user? It could very easily mess up what that other person is trying to do (may get locking deadlocks too).-----------------------SQL isn't just a hobby, It's an addiction |
|
|
|
setbasedisthetruepath
Used SQL Salesman
992 Posts |
Posted - 2002-10-25 : 12:04:12
|
quote:
Curious... Did you know that a temp table is only there for a single instance? You could have the same temp table named #mytemptable created 50 times in a database, each one being used/opened in a different instance.
Sort of, yes. This is awkwardly worded.A temporary table with one # sign before its name indicates a local table. This means that it can only be referenced from the connection it was created on (i.e. local scope). A temporary table with two # signs, such as ##temp, has global scope and can be referenced by any active connection.Internally, SQL Server modifies the name of the table you create with your "CREATE TABLE #TEMP" DDL to enforce local scope. This way, when connection A creates #temp and connection B creates #temp, they will actually have distinct names and will not interfere with one another. This internal name looks something like "#MYTMPTABLE_____________________________122" as talked about above.Jonathan{0} |
|
|
|
M.E.
Aged Yak Warrior
539 Posts |
Posted - 2002-10-25 : 12:19:39
|
| Sorry, I'm akward in general.Still, should you really be querying the temp DB like that anyway? who's saying that you won't pick up someone elses temp table instead of the one your looking for? -----------------------SQL isn't just a hobby, It's an addiction |
|
|
|
setbasedisthetruepath
Used SQL Salesman
992 Posts |
Posted - 2002-10-25 : 12:23:11
|
quote:
Still, should you really be querying the temp DB like that anyway? who's saying that you won't pick up someone elses temp table instead of the one your looking for?
Absolutely, you should not be querying tempdb in that fashion. If that was your take-home message M.E. then I apologize, you're correct in steering the poster away from that method.Jonathan{0} |
|
|
|
|
|
|
|
|
How Can I Find the Temporary Table In vb?