Eric,

I think the problem with using global temp tables is that you can run into problems if two users are running the SP at the same time ... doesn't SQL then use ##temp1 for both users? Not the behavior we want to have ...

~~Bonnie


>I never used a scenario like yours but it seems that both SPs are not running into the same session. Can you use global temp tables?
>
>

CREATE TABLE ##temp1 (xyz char(1))

>
>
>>We're having a problem with Stored Procedures that access Temp tables ... the problem occurs only on one of our customer's servers (using the exact same database, on a different one of their servers, there are no problems).
>>
>>We've traced the problem to something to do with the visibility or scope of Temp tables.
>>
>>Here's an example of what I mean:
>>
>>SP1 has this code:
>>

>>    CREATE TABLE #temp1 (xyz char(1))
>>
>>    EXEC SP2
>>
>>    SELECT * FROM #temp1
>>

>>Now, SP2 contains code that uses #temp1 if it exists:
>>

>>    IF OBJECT_ID('TempDb..#temp1) IS  NOT NULL
>>        INSERT INTO #temp1 (xyz) VALUE ('x')
>>    ELSE
>>        SELECT xyz FROM someothertable
>>

>>What ends up happening is that SP2 does not recognize that #temp1 has already been created and what we're trying to figure out is why. Something to do possibly with *where* Temp tables get created? Isn't it *always* in TempDb? If not, then where is the setting that dictates that?
>>
>>TIA,
>>~~Bonnie