SQL Server 2000 - TempDB Problem?

Hi,

I believe our problem is related to tempDB on the specific server but I would like to know if anybody has come across a similar issue.

We have an SQL statement similar to the following.

BEGIN TRANSACTION

CREATE #tableA

INSERT #tableA SELECT (expression A)

INSERT #tableA SELECT (expression B) WHERE (condition)

COMMIT TRANSACTION

First, let me briefly expand on the second INSERT as this may help when reading the points below.

INSERT #tableA
SELECT ...
FROM ... INNER JOIN tableB
WHERE NOT EXISTS (SELECT ... FROM #tableA WHERE #tableA.columnA = tableB.columnB)

This script works fine on all of our servers except one, which is why I believe tempDB may be involved. After an analysis of the problem, we have the following results,

- If we remove the Transaction, the script succeeds.
- If we leave the Transaction and remove either the first or second INSERT, the script succeeds.

- If we leave the Transaction and both Insert statements and remove the WHERE (condition) from the second Insert, the script succeeds.

- If we reduce the row counts from all source tables concerned by 90%, the script still does not succeed.
- The script had succeeded the week before on the server in question.

Finally, if we replace #tableA with tableA, the script succeeds.

Any help on this would be greatly appreciated.

Thanks.