Results 1 to 5 of 5

Thread: ##Temp tables

  1. #1
    Join Date
    Feb 2004
    Posts
    134

    Unanswered: ##Temp tables

    I have a stored proc that creates a temporary table, then calls several other stored procs to insert data.

    Code:
    CREATE PROCEDURE usp_CreateTakeoff
    @iEstimate int,
    AS
    
    CREATE TABLE ##Temp_Takeoff
    (
           Field1 ......
           Field2 ......
    )
    
    -- Add Structural data
    usp_AddStructural @iEstimateID, 1, 'Structural'
    usp_AddForming @iEstimateID, 2, 'Forming'
    ...
    ...
    ...
    GO
    Now, a couple of problems, after the table is created and populated, I cannot find it in my list of tables, even after "refreshing".

    I checked to ensure that it exists using the query analyzer and it does so I know the table is being created.

    Also, I cannot see the table using crystal reports, connecting etc...... Can I not access a temporary table from 3rd party applications? I have crystal reports 7.0 professional.

    Any ideas?

    Mike B

  2. #2
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Global and local temp tables are created in tempdb.
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  3. #3
    Join Date
    Feb 2004
    Posts
    134
    Quote Originally Posted by rdjabarov
    Global and local temp tables are created in tempdb.
    Now, if I remember correctly, 1 (#) indicates global and 2 (#) indicates local.

    So if multiple users executed the stored proc with ##Temp, then each connection would create a table unique to the connection? So multiple users could execute this proc without interfering with each other?

    Mike B

  4. #4
    Join Date
    Jan 2004
    Location
    In a large office with bad lighting
    Posts
    1,040

    Red face Pounding away at temp tables

    Your memory fails you young padowan. ## is a global temp table which will cease to exist when the last connection to it ceases to exist. The # temp table is a local table for the duration of the process.

  5. #5
    Join Date
    Feb 2004
    Posts
    134
    Quote Originally Posted by tomh53
    Your memory fails you young padowan. ## is a global temp table which will cease to exist when the last connection to it ceases to exist. The # temp table is a local table for the duration of the process.
    Not the first time, won't be the last I am afraid! Thanks for the correction.

    Mike

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •