Thanks for your walkthrough Praveen.
After reading I have this one question :
Is it correct to summarize the article down to the claim that it was caused by someone "changing the SQL service account from services console instead of Enterprise manager". If that hadn't been done - the SP4 would have been applied fine ?
- But you listed the troubleshooting very educationally and I bet it will save other the time and hazzle