Go to Settings > Sound > Notification Ringtone. Change that to whatever you want your notifications to come as (default is Droid) (these are the sounds for when you plug in the USB and other notification tones. Now go to you Text Messages. Press menu > Message Settings. Scroll down to Notification settings and Select ringtone. Change this to what you want your text messages to sounds as.
My phone says "Droid" when I plug in USB and "You have a text message" when I get a text.