It is featured in the new James Bond movie "Casino Royale". It is the horn that is used in the alarm system at the Nambatu Embassy, which Bond visits during his chase scene with Sebastian Foucan. Which others sound like it?
You can see it in this video at approximately 6:20.
http://www.youtube.com/watch?v=t7XiqMFAld8