I think the frustration is actually in some people not using the right tools for the job. I like NoSQL databases (specifically MongoDB), but I have not used them with anything I've written. Why? Because it wasn't the right tool for the job. I tend to use MySQL, Postgres or sqlite because it's so widely available and well known in how to administer. There are times that NoSQL will makes sense, it's just not the area I work in.
I do think we are going to continue seeing an uptick in NoSQL related things since many companies are fixated on "the cloud" while not really knowing what "the cloud" is (heck, no one still really, truly has a common definition of what it means ...). Since NoSQL seems to be a popular tool, and "the cloud" is a popular buzz phrase CIO's/CTO's will likely be pushing their shops to utilize "NoSQL in the cloud". While large scale applications which don't require relational information and need fast syncing across many servers is good grounds for NoSQL, these "NoSQL in the cloud" instances will probably not actually fit that status.
I do agree that it will be a good thing when "NoSQL for everything" dies. Just like it was a good thing when "PERL for everything", "Java for everything" and "Ruby for everything" died, but let's not throw out the whole idea because a lot of people use it wrong.