Hi,

I'm using SQL Server 2000 SP2. I have restored a database. I know someone has changed one of the stored procedures in that database before the backup is done.
So, I execute that stored procedure to see the result. However, the result is still same as before the change is made. At first, I guess the change is not in but I checked the stored procedure and the change is there. Then I guess the changed statement is not executed somehow. So, I use SQL Profile to trace whether it is executed or not. I can see the changed statement is being executed. Then I have no clue why this is happening. So, I execute sp_helptext to get the content of that stored procedure and drop the stored procedure and recreate the stored procedure by using what have returned from sp_helptext. Interestingly, I execute the stored procedure now and it will yield the correct result. To make sure the existence of this weird problem, I have restored this database again, have the same problem, and then have no problem after recreating the stored procedure.

The steps are exactly like this:

1. Restore backup
2. Run the stored procedure
3. Get wrong result
4. Run sp_helptext to get the context of that stored procedure
5. drop the stored procedure
6. Recreate the stored procedure from the result of sp_helptext
7. Get the correct result

I have also tried the following steps:

1. Restore backup
2. Run the stored procedure
3. Get wrong result
4. sp_recompile the stored procedure
5. Run the stored procedure
6. Get wrong result
7. Run sp_helptext to get the context of that stored procedure
8. drop the stored procedure
9. Recreate the stored procedure from the result of sp_helptext
10. Get the correct result

I have tried few other times by executing DBCC CHECKDB, DBCC FREEPROCCACHE (I'm not familiar with this command but its description seems to indicate that it enforces recompilation of all stored procedures).

I have NOT tried the command DBCC DROPCLEANBUFFERS and don't quite understand it.

Thank you for any help,

Peter