What once kept out hordes of bloodthirsty warriors is, nearly 2,000 years later, barely proof against the most timid of sheep. But if Hadrian’s Wall is now low on stature, it remains high on atmosphere, says Harry Pearson.