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