I have a stored proc which works for few execution (all with same parameters), but after some time gives run time error ' Un ambigous column '. If we recompile the proc, it again starts working.. Any clue what could be happening?
The optimiser will occasionally rebuild the plan for a stored proc. If the tables are transaction tables then they might well be empty at some times and full of data at other times. This might affect the plan that the optimiser chooses. Though why it's actually coming up with this error can only be solved by providing us with some code and table definitions.
Perhaps you could snapshot the tables before calling the proc. Also store what, if any, parameters are being passed to the proc. When the proc fails then see if you can run a copy of the proc but on your snapshot tables. This might give you a better chance of reproducing the error.
EDIT: you could also try forcing the optimiser to choose a certain path each time within the stored proc. I don't like this approach but it might work.