Creating Temporary Table 2

[WZUP]

Hello,

I using VB6 and SQL 2000, if I create a temporary table within a stored procedure and it is called from within my program by more than one user, will it overwrite/destory itself if both users access the same program.

The ideal is to use the table for storing and indexing items used for a Order Release procedure and be retrieved in order when the Order is printed out.

If so... is there a way to store each users table in memory to avoid conflicts.

The table's life cycle is only need until each users disconnects.

All suggestions welcome..

Thanks

 

[checkai]

You could give the temporary table a name based on the time it is run or something like that?

DLC

 

[DawleyWeb]

From BOL

"If a local temporary table is created in a stored procedure or application that can be executed at the same time by several users, SQL Server has to be able to distinguish the tables created by the different users. SQL Server does this by internally appending a numeric suffix to each local temporary table name. The full name of a temporary table as stored in the sysobjects table in tempdb consists of table name specified in the CREATE TABLE statement and the system-generated numeric suffix. To allow for the suffix, table_name specified for a local temporary name cannot exceed 116 characters."

DawleyWeb

 

[nigelrivett]

a temp table is specing to the batch in which it is created.
The table name has an extention which is connection specific and includes a sequence number for the batch nest level.

i.e. temp tables created by different users are distinct.

======================================
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.

 

[WZUP]

DawleyWeb,

Thanks for the quick info..

Could you tell me under what topic you found your posting
from BOL.

I've just spent an hour looking before I made my post.

So your saying I could have 6 TempTables named "TEMP" with 6 users running the same time, without any conflict?

 

[WZUP]

Nigelrivett, DawleyWeb

Thanks for your time and helpful info ... can't buy you a beer, but here's a STAR

 

[sunila7]

if you create a local temporary then diff user can have same table name w/o any problems as nigelrivett says in his post. But if u r using a global temp table then it is viible to all users

here is a link to it on MSDN. look for Temporary tables in this article

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_sp_da-di_8c32.asp

Sunil