BOb,

It's being run from our .NET app. As I said, it all works fine on most machines. Works on ours, works on our customer's other machines. He's only having this problem on one computer. It's obviously something peculiar to that one machine, and I'm thinking it might be some kind of setting in SQL Server ... but I have no clue what that might be.

~~Bonnie


>Bonnie,
>
>Are you running the SP's from Query Analyzer or an ap?
>
>I had a simlar problem, and I don't know if it was the SP, or just me... but I don't remember changing it. In my QA connections setup I had somehow set CLOSE CURSOR on COMMIT which also made the temp tables seem to go away, if I remember correctly.
>
>BOb
>
>
>>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