Open the sms app. Go to menu>settings. In there you will see notification settings for everything including that specific notification. Just uncheck it.
Just got into the settings of the messaging app and turn off all notifications.
I have eliminated them, but still can't find a way to get rid of the number of "unread" messages listed on the messages icon. I use SMS Popup, and I click close when reading the popup. I don't have a notification of the message, but the number of unread is still there. There doesn't appear to be an option to get rid of it.
I don't care for Handcent, but I may be forced to switch if this annoyance doesn't have a fix