Infinite recursion, its pretty nasty stuff

I always thought the best one was when they made a game from Street Fighter The Movie.

IIRC Valve put some advertisements into Counter-Strike, but they advertised only their other games (Orange box, etc.). I'm not sure when (if even) they patched it away.
However that was not that annoying as this what Aliasalpha has found.

I can see it now. I will be playing Mass Effect 2 one day and Shepard will say "I'm Commander Shepard and when i'm on the Citadel I always quench my thirst with a SPRITE"

This ad is annoying because it wasted my bandwidth but isn't radically out of place. Commander shephard drinking sprite WOULD be,

I dislike adverts in games, and will try and find a way to block them or texture mod them out. Not always possible depending on how it is done, but a number of times I've quit playing a game to figure out any way to do it.
Some genres (like racing) I realise it's part of the whole racing thing, and rarely do I notice any of it. Not much interest in actual cars and racing probably helps, even if in game form it's a guilty pleasure.
For products I know, like and use it annoys me more. So I much prefer 'Generic Cola' to 'Coca Cola' despite drinking more coke than I should.

No, Dr Pepper. Get my horribly made joke about ME2 DLC exclusive to a certain soft drink?

I think it's okay when it's done with enough tongue-in-cheek. For instance, in the movie adaptation of Hitman, he swings through a window into a hotel room, walks through the room and leaves. In the room are two kids playing Hitman 2. They look at him, look at each other, then look at the screen. http://www.youtube.com/watch?v=y0SotzH2Whk (around 0:30).

Better than nothing anyway.....

I've seen some adverts while playing Prototype including Clash of the Titans, and I think I saw one for I think I also saw one for Sherlock Holmes, but then again I was running by really fast.
It is really jarring to see these in a game and does nothing more than rip you out of the experience. Especially since they don't serve to make the games in question any cheaper, it's just annoying

That is kinda true.

Yep, a certain exploitable DLC process... I see what you did there.

I recently played Enemy Territory: Quake Wars, and it had in-game advertising as well, to my surprise. It was also advertising for Clash of the Titans. Now, I really couldn't care less about in-game advertisement as long as it's worked in, like in racing games or on billboards and such, but in ET:QW it was just a texture on the wall, as if someone had used it as a spray in a Valve game. It was quite distracting.

It's not like I oppose the ads on principle... If they you're in a location, in-game, where you'd expect ads in real life, it's cool, in a way, to see real ads. I mean, now I can get annoyed in a game by distracted advertisements just like in real life! I'm not about to complain about developers finding a way to make some extra cash, either.
Streaming new ones without telling you is pretty dirty, though.

This has been around for a while