I don't think you can use a ring tune the same way for sms as you can for phone calls. To change the notification tone for sms, hit the settings button while in the list of you texts, not a an individual message thread, and go into settings. Go into notification setting and you'll be able to change the tone in there. My favorite ones are all from a Legend of Zelda soundboard off the market.

 

RingDroid is a good app
Ringdroid - Android Application on the Android market

 

theres an easier way and you dont have to download an app either (unless you want to that is).
Firstly connect you phone to your pc and set the sd card to mount.
Go to my computer and locate your sd card.
If you have already got music on your pc the do the following:-
go to the folder that contains your song that you want for sms tone
right click and copy this file
go to your sd card and select the folder MEDIA. inside media select AUDIO then select NOTIFICATION and copy the file into here.
Disconnect your phone
on your phone go into your messages and press the left nav button to select the settings option
go to noftification tone and press.

find the file that you copied and select it
Hey presto you should have this tone as your sms tone

any probs give us a shout or if you need to know any other way.

And yes i have got ringdroid as well (but i only use this for shortening mp3's)