temp table collision in a stored procedure


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 2 of 2

Thread: temp table collision in a stored procedure

  1. #1
    Keoki12 Guest

    temp table collision in a stored procedure


    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

  2. #2
    DaveSatz Guest

    Re: temp table collision in a stored procedure

    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




Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center