I tried everything I could to get this to work, and I finally figured it out.
I have an HTC Hero and the folders for notifications weren't working for me. I figured out how to do it.
1. Mount your phone to the computer.
2. Open the folder to view files.
3. Create "media" folder, then "audio" folder within that, followed by 4 folders:
4. Put any mp3 you wish to use for your text messages into "sounds"
5. Remove phone from computer.
6. On your phone, go to your messages (where you get all your text messages).
7. Hit menu on your phone.
8. Go to settings.
9. Scroll down under notification settings.
10. Go to "select sound"
11. Select the song you put on there.
It worked for me!