unique temp table - SQL Server Forums

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

Transact-SQL (2000)

unique temp table

Author

Topic

mdelgado
Posting Yak Master

141 Posts

Posted - 2003-10-30 : 13:35:04

I have a stored procedure that creates a #temp table. When the sp is executed at the same time, the user gets an error.

I can think of a complicated way to create a unique temp table using datetime, but I just wanted to ask to see if anyone has a better way of doing it.

thanks.

homam
Starting Member

31 Posts

Posted - 2003-10-30 : 14:59:37

I'm not sure how this could happen with non-global temp tables. Temp tables are context-specific. So whatever temp tables are created inside the context of a stored procedure, they're not visible outsite it and they get dropped after it finishes execution. For example, this stored procedure creates a temp table and doesn't explicitly drop it. So it doesn't matter how many times you call it, it won't generate an error:


create procedure UseTempTable
as
begin
	create table #test (a int, b int)

	select table_name from tempdb.information_schema.tables
	where table_name like '#test%'
end
go

create table #temp (a int, b int)
exec UseTempTable

When you run the stored procedure, you will see that it display two temp tables in tempdb but with different names. So the outside table doesn't conflict with the in-proc table.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-10-30 : 15:02:54

mdelgado, there must be something wrong with your stored procedure because the temp tables are unique to the session. You might want to post your code so that we can see what is wrong with it.

Tara

mdelgado
Posting Yak Master

141 Posts

Posted - 2003-10-30 : 16:01:02

Sorry, I should have been more specific.

I'm generating a Global Temp Table (i.e. ##SQL) with a sp that returns a resultset.

This sproc is run everytime a page loads on our intranet. I multiple people load the page at the same time, the sproc tries to create the same GLOBAL table and thus the error.

PS, I know I showed a local temp table in my original question, but what I meant to type was a GLOBAL temp table.

Sorry....

geossl
Yak Posting Veteran

85 Posts

Posted - 2003-10-31 : 02:24:32

Timestamp may also have a chance of colliding name.
Is it possible to use GUID?

nr
SQLTeam MVY

12543 Posts

Posted - 2003-10-31 : 03:07:23

You can use the spid as part of the name but that will mean that all access will be via dynamic sql.
Better to redesign so it doesn't use a global temp table.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

mdelgado
Posting Yak Master

141 Posts

Posted - 2003-10-31 : 07:11:24

Redesigning is not an option. Can anyone offer me some advice on the best way of doint this?

thanks.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources