i think the problem for him was no one knew what to do with him,
he scored a few screamers so moyes tried him out as the next gerrard but just frustrated by having every shot blocked
tried holding midfield but couldnt really tackle or intercept so he was just sort of there playing sideways passes and trying long shots that never came off
Moyes ruined Rodwell by not giving him a position and hanging him out to dry in positions it was obvious he was never capable of playing. He had him play Right Wing at 1 point, as he did with lots of players who weren't right wingers, or even wingers... or even midfielders for that matter.
He also forced him to alter his natural positive approach to games, as he did with lots of players. He started out, as you rightly say, shooting from long range, scoring a few screamers in the process, and making surging runs from midfield, ala his goal against Utd, both qualities Moyes frowned upon. After scoring a late, long range winner against Birmingham one year, Lee Carsley stated in his interview that he was pleased it went in as Moyes had told him to stop shooting from long range, something I'm sure wasn't restricted to Carsley.
After very visibly becoming more Conservative, and choosing the safe, sideways pass after being made an example of on numerous occasions, Rodwell no longer showed any of the qualities that had initially made him stand out, and instead, became just another "sitter" like Neville.
After joining City, he actually got a bit of that back, scoring some quality goals, but he found it hard to dislodge Toure in the City midfield, as you can imagine, and combined with some niggling injuries, which were rooted in playing when he shouldn't have for us in years previous, as was the case with a lot of our younger players, he became surpluses to requirements as City continued to strengthen and improve.
Sunderland was the wrong move for him, and I always felt he should have come back here and tried to convert to the Centre Half he was initially earmarked to be. Him and Stones could've made quite the partnership, both being comfortable ball playing defenders, both physically imposing, athletic and quick.
Its sad that he is reduced to this now, but as someone stated earlier in the week, it surprises me that more players don't have similar experiences. They get so much so quickly, and the motivation to play must be very hard to ignite, especially when playing poorly for a poor team. It takes a certain kind of mentality to become a top player, regardless of ability, which is why it's not always the best or most talented players that succeed, rather those that knuckle down and have a single minded stubborness to always play and improve.