I had one once. It wasn't bad. The drugs they pumped into me were great. When I woke up, I felt unsteady but not in a unpleasant way. I guess you could say I was walking into walls, but I was thoroughly enjoying it.
Interestingly, I was later told by somebody that the drug in question doesn't put a person out, but makes it so that they have no memory of a certain period of time. I don't know if that's true, but if it is, I can only imagine what I said while they had a tube up my ass. One of the nurses in the room was VERY cute.
The worst part about the procedure was the preparation. I don't remember the exact details, but aside from the usual fasting, I had to take something that was supposed to clean me out, and I had to use a fleet enema at least once. I could have done without the enema.