Local temp table names in the sysobjects not same

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.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Local temp table names in the sysobjects not same

Author

Topic

heze
Posting Yak Master

192 Posts

Posted - 2006-09-21 : 11:33:32

hi
When I create a local temp table and the look for it in the sysobjects table, the name is not the same instead of being
#myTable
its recorded as:
#myTable____________________________________________________________________________________________________________00000000151F

Is there a way to get the sysobjects to have exactly the same name?

following is my code:

select *
into #myTable
from someOtherTable

--
select * from tempdb..sysobjects
where type='U' and [name] like '#myTable%'
--

thanks

heze
Posting Yak Master

192 Posts

Posted - 2006-09-21 : 11:36:06

forgot to include that my guess is that since a # table is local to a specific session, maybe the table name section ...___00000000151F is specifically the individual session data

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-09-21 : 12:15:29

quote:
Originally posted by heze

hi
When I create a local temp table and the look for it in the sysobjects table, the name is not the same instead of being
#myTable
its recorded as:
#myTable____________________________________________________________________________________________________________00000000151F

Is there a way to get the sysobjects to have exactly the same name?

following is my code:

select *
into #myTable
from someOtherTable

--
select * from tempdb..sysobjects
where type='U' and [name] like '#myTable%'
--

thanks

Why do you feel you need to do this?

CODO ERGO SUM

heze
Posting Yak Master

192 Posts

Posted - 2006-09-21 : 12:50:01

Hi MV, well I have a procedure that will be executed from a web page in which a temp table was necessary, in addition, I am using the information schema of the table since Im building dynamic sql and I need the names of the fields. If I use a ##temp table, the procedure will result in an error if 2 pages execute the proc concurrently, therefore I used a #table,
therefore, to refer to the specific temptable in tempdb.sysobjects, I need to refer to the specific #temp table I created.

the name is #myTable_..._00000000151F, I am struggling to find a way to query the "_..._00000000151F" section from somewhere to be able to refer to the specific table, I believe its the session data, which I yet don't know were to find

thank you

heze
Posting Yak Master

192 Posts

Posted - 2006-09-21 : 13:04:10

After doing some queries seems that whatever one does to the temp table SQL Server seems to automatically recognize the sepcific #temptable that was created by that specific session (obvious now) so I really dont think i need to know anything about the "_..._00000000151F", just use the #myTable name and SQL server will take care of concurrent manipulation of #myTable , if no one says opposite...

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2006-09-21 : 17:23:01

heze, that's correct. You answered your own question earlier too when you said that you couldn't use ##temp table - SQL Server has to keep the names unique so that they don't clash. #temp tables are local to a single connection but what if a hundred connections all have the same #temp table name at the same time - SQL Server therefore assigns that extra piece to the name to keep the names unique, and it takes care of giving each connection the correct table when they use the #temp table name.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources