Local temp tables are application or user specific - which means that they are only visible to the connected user. Also, the server usually drops them when the user disconnects. Could this be part of the problem in your environment?
>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
Al Williams
Anola MB, CANADA