I have the following scenario. I have a stored procedure that refers to a table of data. Processes that data, inserting records into other tables,and then drops the original table.
What is happening is that the stored procedure is somehow getting bad data when it reads the table. I say this because the data entered into the other tables is wrong but if you look at the original table it is OK. Specifically, it is reading floats wrongly. Other varchar fields are OK.
If I create the table, recompile the stored procedure and then run it, it works. However, the next table breaks it again.
The procedure has to be compiled while the data table that will be processed is in existence. Note this must be the real data. An empty table of the same structure doesn't work.
How can I get the stored procedure to work repeatedly. I have tried creating the procedure with recompile, this makes no difference.