Apologies if I'm a bit late to the conversation. I had this problem since getting Froyo (already forget when that even was, ha.) and accidentally fixed it like a week ago.
So I had some custom-made notification sounds that we'll call notification A, B, C, and D. I had set notification A to be my default notification in settings > sound settings > notification ringtone; notification B to be for new sms; notification C to be for new email; and notification D for an IM app. Yet no matter what type of notification was supposed to be played, everything played notification A instead of the notification I had specified for each specific app. Pretty annoying.
(The only solutions I could find online were dozens of people suggesting a master reset, because apparently I'm an idiot and don't know how to find things on google.

)
Anyway I eventually found that if I changed the default notification to
any of the phone's preinstalled tones, the correct user-specified tone is now playing in each app. But for some reason, if any user-created tone is set to be the default, the phone will play the default tone for ANY notification.