Sweet Scent causes wild Pokémon to appear in clear weather conditions while standing. If Repel is in effect, Sweet Scent is prioritised.
Sweet Sent works anywhere that has wild Pokemon appearing naturally. The only places you can't use Sweet Scent are outside of grass/ water or in weather conditions other than clear.
I'm pretty sure I used Sweet Scent on water before, but I forget which generation it was and whether that's changed.