I recently started another thread that got resolved, but this question is closely related:

I have a stored procedure that performs these steps:

1) Create a temporary table.
2) Copy everything from a view into that temporary table.
3) If that works out, clear out a non-temporary table.
4) Copy everything from the temporary table into the non-temporary table.

This sequence of steps is technically performed four times, though one of those times may have nothing to do with this. In each iteration, the temporary table created in step #1 has a different set of fields (because it's working with different data), but otherwise each iteration is identical.

I'm trying to track down a problem in the SQL of the fourth iteration, and it's a tedious process that requires me to run the stored procedure over and over to see whether or not I fixed the problem (one out of many dozen fields is too short an nvarchar, but figuring out which one is a chore). After working on this for several iterations, SQL Server suddenly horfed up a whole series of errors. The meaning of the errors was clear: When step #1 was attempted for the first iteration, rather than creating the temporary table, it was reverting to using the schema of the temporary table used in the fourth iteration (the last one run).

Each iteration begins like this:

BEGIN TRY
IF OBJECT_ID('TestTable', 'U') IS NOT NULL
DROP TABLE TestTable

CREATE TABLE TestTable

So, it looks for TestTable, and if it is there it drops it. It then creates TestTable with the new set of fields for this iteration. This worked fine for several runs, then it suddenly stopped dropping the table and creating the new table. At first, we thought this was due to having desperately low memory on the SQL Server box (low enough that the whole server should have borked), as the problem vanished once the memory issue was cleared up. However, it showed up again now that we have plenty of memory. When looking at it the first time, my boss did something else, unrelated to the memory. This time, we tried that unrelated solution again, and it fixed the problem again.

The solution was to copy the DROP Table TestTable and run it as a stand alone query. That fixed whatever the problem was, and all went back to normal.

So, the question is: What happened, and how do I keep it from happening again?

It looks like SQL Server may have optimized the execution and optimized away the creation of the test table, because it already existed, or for some other reason. It then moved on to an INSERT statement and bombed out because a whole lot of the fields were wrong. I'm a bit nervous about the fact that the procedure ran correctly (well, mostly correctly, since I still have that string length issue to work out) several times, then suddenly failed. I'd rather not have that happen routinely.