Short Answer: No, there is not an app for sending SMS over SIP.
The long answer: check with your provider. The solution propsed @ SIP-Implementers circled around using SIP IM to send and receive SMS messages. Since Conversations supports SIP IM, a provider that implements a SIP <-> SMS gateway may be able to provide you SMS through N900 Conversations.
But in all honesty, SIP-Implementors came to the correct conclusion: use email.