How was "Sinners in the Hands of an Angry God" effective and ineffective? How would you react?
Accounts of the reception of Edwards's sermon that day in 1741 maintain that some in the congregation cried out, some fainted, and some wept. In the short term, it is plausible to assume it shook people up and made them reexamine the ways they were living and thinking about their afterlife. In that way, it could be deemed effective.
It could be said that Edwards's sermon, perhaps a centerpiece of the Great Awakening in the 1730s and 40s, did little to staunch the flow of people abandoning the vestiges of Puritan thought present in his theology. In that way, it was ineffective. As more people came to the colonies, many for reasons unrelated to religious freedom, what came with them were other ways of worship and attitudes toward the role of religion. The Enlightenment greatly influenced the rise of rational thought, and Deism became a more comfortable way for some to think about God.
The last part of your question is more difficult to answer; it's not easy for a person of the 21st century to fully understand the outlook of a person who would have attended this sermon. Since you asked, though, I would say the sermon would be a turn off for me.