I want to know if we can send sms using sql server 2008 r2.Construct the message and
send it through sql database itself.If yes, how can i go about this.
Yes you can! Use the send mail task and then send via email. You need cell numbers and providers for each person you want to message.
http://en.wikipedia.org/wiki/List_of_SMS_gateways Use this list and just add the phone number to the corresponding cell provider.
Please mark answered if this answers your question.
You would use and SSIS package.
You can send email natively from SQL Server via Database Mail (2005+) but not SMS natively. Check this thread for more ideas: http://stackoverflow.com/questions/2047536/sending-an-sms-through-sql-server-2008
Owen's idea is also possible, Pragmatic Works has a task in Task Factory that allows you to send SMS via SSIS packages: http://pragmaticworks.com/Products/Business-Intelligence/TaskFactory/Features.aspx#TS10
I have used both the msdb.dbo.sp_send_mail and SSIS Send Mail task to send a text to my phone without any special set up. The only issue I run into is the message will be truncated when it is too long; greater than 160 characters. I used a list like the one on the link posted above by Owen to figure out the right recipient formatting, but that was all.
Thanks. Will go through it
There may be an easier way for you depending on what you are looking to do using a service called Twilio.com.
Check these two links out for more information on making telephone calls and sending text messages from a single line of SQL code.