How do I sent text messages from SQL?

  • Rod at work

    SSC-Dedicated

    Points: 33137

    I know that it is possible to set up SQL Server to send you text messages depending upon the outcome of a SQL job. So I would think it would be possible to write some SQL routines to send users text messages. How is that done?

    Kindest Regards, Rod Connect with me on LinkedIn.

  • LutzM

    SSC Guru

    Points: 107049

    If you're talking about sending an email using SQL Server you might want to look up sp_send_dbmail in BOL.

    If your system is not configured yet to send mails, you should read the "Database Mail Configuration Wizard" section first.



    Lutz
    A pessimist is an optimist with experience.

    How to get fast answers to your question[/url]
    How to post performance related questions[/url]
    Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

  • Lowell

    SSC Guru

    Points: 323396

    the problem is unless you have your own SMS service, you can't do SMs messages to cell phones;

    while every carrier has a supprt email that allows an email to be sent to a cell phone number, you have to KNOW which carrier a phone number belongs to.

    it used to be easy...the cell phones were registered to each carrier...but with the advent of keep your phone number when you switch, it's a lot harder.

    once you have a persons 10 digit phone number, you can a 150 character email, including the SUBJECT line and send it to their carrier;

    i typically leave the subject blank and use 150 char BODY for my messages.

    once you have sp_send_mail set up and working, this might get you started:

    use http://www.data24-7.com to find out which carrier a number is:

    AT&T – cellnumber@txt.att.net

    Verizon – cellnumber@vtext.com or phonenumber@vzwpix.com

    T-Mobile – cellnumber@tmomail.net

    Sprint PCS - cellnumber@messaging.sprintpcs.com

    Virgin Mobile – cellnumber@vmobl.com

    US Cellular – cellnumber@email.uscc.net

    Nextel - cellnumber@messaging.nextel.com

    Boost - cellnumber@myboostmobile.com

    Alltel – cellnumber@message.alltel.com

    Cincinnati Bell Wireless it’s cellnumber@gocbw.com

    Metro Pcs 14352345762@mymetropcs.com

    Lowell


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • Rod at work

    SSC-Dedicated

    Points: 33137

    LutzM (9/27/2010)


    If you're talking about sending an email using SQL Server you might want to look up sp_send_dbmail in BOL.

    If your system is not configured yet to send mails, you should read the "Database Mail Configuration Wizard" section first.

    I've got email set up. Been working well for a while now. Thank you for the reference go sp_send_dbmail!

    Kindest Regards, Rod Connect with me on LinkedIn.

  • Rod at work

    SSC-Dedicated

    Points: 33137

    Lowell (9/27/2010)


    the problem is unless you have your own SMS service, you can't do SMs messages to cell phones;

    while every carrier has a supprt email that allows an email to be sent to a cell phone number, you have to KNOW which carrier a phone number belongs to.

    it used to be easy...the cell phones were registered to each carrier...but with the advent of keep your phone number when you switch, it's a lot harder.

    once you have a persons 10 digit phone number, you can a 150 character email, including the SUBJECT line and send it to their carrier;

    i typically leave the subject blank and use 150 char BODY for my messages.

    once you have sp_send_mail set up and working, this might get you started:

    use http://www.data24-7.com to find out which carrier a number is:

    AT&T – cellnumber@txt.att.net

    Verizon – cellnumber@vtext.com or phonenumber@vzwpix.com

    T-Mobile – cellnumber@tmomail.net

    Sprint PCS - cellnumber@messaging.sprintpcs.com

    Virgin Mobile – cellnumber@vmobl.com

    US Cellular – cellnumber@email.uscc.net

    Nextel - cellnumber@messaging.nextel.com

    Boost - cellnumber@myboostmobile.com

    Alltel – cellnumber@message.alltel.com

    Cincinnati Bell Wireless it’s cellnumber@gocbw.com

    Metro Pcs 14352345762@mymetropcs.com

    What?! You're kidding! Well, obviously you're not, so I guess that just puts the kabbosh on the whole thing.

    Kindest Regards, Rod Connect with me on LinkedIn.

  • SQLRNNR

    SSC Guru

    Points: 281210

    Lowell does it pretty much how we did it. Now we just send emails to smart phones instead.

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • Rod at work

    SSC-Dedicated

    Points: 33137

    CirquedeSQLeil (9/27/2010)


    Lowell does it pretty much how we did it. Now we just send emails to smart phones instead.

    My problem is I can just about guarantee that most of the people I'd want to send an email to won't have smart phones.

    Kindest Regards, Rod Connect with me on LinkedIn.

  • Steve Jones - SSC Editor

    SSC Guru

    Points: 715841

    I do it as Lowell does it, email to the SMS number for the person.

    One thing to note, SMS is guaranteed. And might not be delivered, and you won't know. It mostly works, but I've had messages be delayed for hours. I might send email + SMS, just to be safe if this is for alerting.

  • LutzM

    SSC Guru

    Points: 107049

    Steve Jones - Editor (9/27/2010)


    I do it as Lowell does it, email to the SMS number for the person.

    One thing to note, SMS is guaranteed. And might not be delivered, and you won't know. It mostly works, but I've had messages be delayed for hours. I might send email + SMS, just to be safe if this is for alerting.

    Sounds somewhat contradictory... Or is there a NOT missing? 😉



    Lutz
    A pessimist is an optimist with experience.

    How to get fast answers to your question[/url]
    How to post performance related questions[/url]
    Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

  • henderson.austin

    Say Hey Kid

    Points: 690

    I am little late to this party but I wanted to throw this out for consideration. There is a service called Twilio.com and they have a really cool API for making telephone calls and sending text messages for almost nothing. I built a small DLL and some UDFs to wrap the calls to their API into SQL code. The project is free to download and you can beplacing telephone calls and sending text messages from your SQL server in minutes.

    Not sure if you have a solution in place now or not but this may be a valid option for you. You can even use the dll to check the status of the TXT message which is pretty neat and cannot be done if you relay through SMTP.

  • Rod at work

    SSC-Dedicated

    Points: 33137

    henderson.austin (12/1/2011)


    I am little late to this party but I wanted to throw this out for consideration. There is a service called Twilio.com and they have a really cool API for making telephone calls and sending text messages for almost nothing. I built a small DLL and some UDFs to wrap the calls to their API into SQL code. The project is free to download and you can beplacing telephone calls and sending text messages from your SQL server in minutes.

    Not sure if you have a solution in place now or not but this may be a valid option for you. You can even use the dll to check the status of the TXT message which is pretty neat and cannot be done if you relay through SMTP.

    Thank you for this information, Henderson.

    Kindest Regards, Rod Connect with me on LinkedIn.

  • henderson.austin

    Say Hey Kid

    Points: 690

    I hope the project helps folks. It is a very cool service and the UDFs make it super simple to use. There was a comment eariler in the thread about not being able to check delivery - using SMS you can check delivery and using this hook to the API you would be able to see that in most situations. There are odd cases but it would show it in most. To be sending SMS and placing telephone calls from a SQL server in literally 5 minutes is pretty cool I think. Twilio gives $30 in credit just for opening an account - this means you can send 3K text messages before you have to pay them anything.... plenty of time to try it out.

    Reminder - I make nothing from Twilio - I am just a fanboy here.

    Hope you all have a great day.

    tw: @kahenderson

    blog: http://www.austinhenderson.com

Viewing 12 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply