temp table collision in a stored procedure

[Keoki12]

I have a stored procedure with 6 temporary tables, all created with the "#"
at the start of the name as they are to be local to the stored procedure
using them. During some heavy testing, 6 users from the VB application accessed
this stored procedure. The result was the database locking. If only 3 users
accessed the stored procedure from the VB application, the process was successful.

My understanding of sql server is that temporary tables are private to the
stored procedure and the connection calling them, which means each user should
have their own temporary table with whatever extension added to the table
name that sql server adds.

But from some reason if the larger group simultaneously access the stored
procedure, everything stops on the same temporary table (Number 4 of 6).

Has anyone else had this problem? Our connections are made via MTS and DTC
is running. Ideas are welcomed.

Thanks

[DaveSatz]

Sounds like it could be an issue w/connection pooling. In my VB/MTS
experience we used the same connectstring for all connections, thus the same
session can be used to run the stored proc. Having said that if you create
the temp tables inside the stored proc that (pooling) should not matter.
You many want to post the SP and whatever is needed to help
--
HTH,
David Satz
Principal Software Engineer
Hyperion Solutions
->Using SQL Server 7.0 SP3/6.5 SP5a/Cold Fusion 4.5.1 SP2/VSS
(Please reply to group only - emails answered rarely)
-----------------------------------------------------------------

"Keoki12" <gb@cardinalhill.org> wrote in message
news:3b0bf840$1@news.devx.com...
>
> I have a stored procedure with 6 temporary tables, all created with the
"#"
> at the start of the name as they are to be local to the stored procedure
> using them. During some heavy testing, 6 users from the VB application
accessed
> this stored procedure. The result was the database locking. If only 3
users
> accessed the stored procedure from the VB application, the process was
successful.
>
> My understanding of sql server is that temporary tables are private to the
> stored procedure and the connection calling them, which means each user
should
> have their own temporary table with whatever extension added to the table
> name that sql server adds.
>
> But from some reason if the larger group simultaneously access the stored
> procedure, everything stops on the same temporary table (Number 4 of 6).
>
> Has anyone else had this problem? Our connections are made via MTS and
DTC
> is running. Ideas are welcomed.
>
> Thanks