When you run an sp for the first time it generates a query plan.
Sounds like it is getting the database ID at this time and so failing when the database is dropped. It will be accessing the old database after it is renamed.
You can probably cure this by specifying 'with recompile' so that it resolves the objects on each execution.
If you are runing the query from query analyser then it must be caching the query plan somewhere.
I do have included recompile option and as you said, it is caching in procedure cache and even after clearing cache also it is resulting same error. I have observed one thing is that, it is not happening with all tables which are included in my select statements in stored procedures. Another thing is after executing Job for 3 to 4 times only this problem is raising and for your information, both servers are having SQL7 SP2 and MDAC 2.5 SP1 (This configuration is forced to keep since most our major clients are running under same environment). Please let me know if you have any answer. Thanks for your kind help