Yeah I remember when it came out it had a mixed reception, especially since it was the same year people were going crazy over Final Fantasy X & Metal Gear Solid 2. I'm guessing a lot of reverence people had for SH2 was because it was the first and only SH game they played. I remember playing it when it came out it and loved it, but thought the first game was much scarier.
You're right about SH3, the developers went back to the lore after fans complained how disconnected SH2 was and instead of trying to outdo SH2's brilliant story, they focused on things they could improve-- namely the gameplay, scares and a more appealing main character which is a definite improvement. James wasn't meant to be a character you'd relate to while Heather is sassy, smart and resilient and the gameplay of SH3 felt more balanced akin to a classic Resident Evil than the (intentionally) shambling slog of SH2.
Don't get me wrong, it kinda sounds like I was trashing SH2 there and that's not the case at all-- I LOVE SH2 but I think the reverence for it is a bit disproportionate which is a shame since there are plenty of merits to be discussed about the other games.