Story telling in games seems to be on a decline in games (looking at you, CoD/BF) but there are still plenty that can do it well. Environmental story telling is favoured because there's no stop to the game play. That's what a game's original intention is, anyway: to game. That said, when a game has a great story but executes it poorly, it can be very jarring.
An example of this is Star Wars: the Old Republic. I think the story for the game is actually pretty decent. But I think it's executed rather poorly. The main reasoning for this is because the abrupt, constant changes in pacing. Over and over again it's like the game has two modes: intense action, then sit around for 5-10 minutes talking and making choices. There are probably more examples of that, but something like that can be really grating once you go through it so much.
Sometimes, I just want to kill things. Sometimes, I just want to get involved in deep thought from a story. This game's divided effort makes it feels as if it half-asses both of these things.
But at the end, all that matters is if the game is good objectively. If the story telling and game play can be intermingled perfectly, the game will be great.