January 19, 2011 at 3:53 am
hi all.
i need to configure our application to send an sms through sql server 2005.Is it possible to do so without dependency from front end programming(java,c#,Vb).
Please help me out of this!!!!
Thanks in advance.
January 19, 2011 at 11:08 am
SMS?
Not sure what you mean by that. Could you translate for those of us who aren't coders?
January 19, 2011 at 11:35 pm
hi,
thanks for u r feedback.i couldnt frame my doubt clearly.Now i try to clarify.....
i have to send message service to mobiles(sms) through smsgateway using smpp protocol --to the users of our application using sql server 2005(from DB -back end)
For this feature to happen i need a solution which is independent of front end programming.
Thanks..
January 19, 2011 at 11:44 pm
Have a look at the below link,
http://www.codeproject.com/KB/database/SMS_message_from_SQL.aspx
I hope it will provide you some help.
Satnam
January 20, 2011 at 2:51 am
hi,
Thanks for your advice.....
January 21, 2011 at 5:57 am
Why not use the built in msdb stored proc. to send the sms text messages? Use the phone number (text msg and proper extension (ie. 2225557777@vtext.com for Verizon) for the @recipients parameter.
BOL:
sp_send_dbmail [ [ @profile_name = ] 'profile_name' ]
[ , [ @recipients = ] 'recipients [ ; ...n ]' ]
[ , [ @copy_recipients = ] 'copy_recipient [ ; ...n ]' ]
[ , [ @blind_copy_recipients = ] 'blind_copy_recipient [ ; ...n ]' ]
[ , [ @subject = ] 'subject' ]
[ , [ @body = ] 'body' ]
[ , [ @body_format = ] 'body_format' ]
[ , [ @importance = ] 'importance' ]
[ , [ @sensitivity = ] 'sensitivity' ]
[ , [ @file_attachments = ] 'attachment [ ; ...n ]' ]
[ , [ @query = ] 'query' ]
[ , [ @execute_query_database = ] 'execute_query_database' ]
[ , [ @attach_query_result_as_file = ] attach_query_result_as_file ]
[ , [ @query_attachment_filename = ] query_attachment_filename ]
[ , [ @query_result_header = ] query_result_header ]
[ , [ @query_result_width = ] query_result_width ]
[ , [ @query_result_separator = ] 'query_result_separator' ]
[ , [ @exclude_query_output = ] exclude_query_output ]
[ , [ @append_query_error = ] append_query_error ]
[ , [ @query_no_truncate = ] query_no_truncate ]
[ , [ @mailitem_id = ] mailitem_id ] [ OUTPUT ]
December 1, 2011 at 7:56 pm
Here is another option for you - it will get you the same thing in just a few minutes plus allow you to make phone calls from your sql server. There is a service called Twilio.com and they have a really neat 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.
December 2, 2011 at 5:39 am
1st you're responding to an old thread and it seems your response is more as an advertisement.
2nd, your response doesn't even seem legimate and it is almost falls on the upsurd side and a huge security issue.
December 2, 2011 at 6:33 am
Steve - I am really confused by your response.
I understand it is an old thread but you have to remember that threads like this are not just for the benefit of those in the thread, but rather thanks to search engines - everyone on the net as long as the site it running. This is a common problem people have - how to send sms through SQL server. This thread shows up high in the search lists because of SEO - so I wanted to comment on it. I commented because I had the very same problem the users here had, but I solved it differently.
The original question is how to send SMS message through SQL server so that the front end application does not have to do the labor and it can be framework agnostic. The most common way this happens is by relaying through an SMTP protocol. This sort of works but only if you know the carrier of the mobile number - and that can change. I work for a company that manages these sort of interactions all the time, people change carriers and then our notification would break... even though their number stays the same. SO I say all of this to say that SMTP works if you have a static list of numbers (say a department that needs alert) but it doesnt scale well.
There are complicated solutions on the market for sending SMS messages, but they are expensive to get into and they really are not necessary. The cloud based API of Twilio (I have no interest in them other than that they have great technology) allows you to have ZERO upfront costs and allow your application to make telephone calls and send SMS messages for dirt cheap. I wanted the same thing this thread wanted - to send text messages through sql server regardless of the carrier - so I wrote a wrapper to call this API from Twilio.
With regard to it being upsurd - I am not sure I agree - nor do I agree that it is absurd. There are security risks with installing any sort of software - there always will be. But extending SQL functions through CLR is a common tactic to accomplishing goals that are complicated in straight TSQL, and the good news is the code is ONLY called when you call the UDF - so it isnt like there is this service running in the background or something stealing your data. The DLL function is wrapped into a UDF and the call returns in < 1 second. I suppose if you wanted to you could use xp_cmdshell() and maybe wget to construct the XML request and http message if you want - so in that respect there are other ways you can use Twilio services without my dll - I just make it easy to do it.
I have been building applications that use SQL in enterprise environments for over 11 years, I have applications that scale into hundreds of millions of rows using distributed infrastructures and I assure you that I have no ill intent with this post or the library. I take a lot of pride in my work and I saw this post as a good way to connect the users of the thread with an alternative solution to the SMTP relay option.
The library is free to download and use.... so in one sense it is pointing you to a product - but it is free to use. If you want to contribute to the cause (which I happen to think is a good cause) then I will speed the library up for you. Its simple really - I am just pointing to an alternative solution, you are not forced to use it, but you may want to give your comments a second thought.
In contrast the question was based on how to send using SMPP - the answer you provided was technically not in line with the question because you answered on how to relay through SMTP. So in that sense both of our options are not actually directly inline with the question right? I think both are valid options on how to solve the problem though.
Thanks for your time board.
December 2, 2011 at 7:05 am
My apologies on the misspelling and I'm glad your response was not nefarious, so an apology is in order for my reply, as well.
February 12, 2012 at 8:28 pm
Are you selling a service of teaching or implementing texting via MS SQL Server?
My goal is to go through a list of phone numbers in a table and and send a different mesaage to each one... the cell numbers are mix of Verizon and ATT service. I can't get there from this string.
Thanks>Matt
February 12, 2012 at 9:01 pm
I am not selling really anything to be honest. I have really found the services of twilio.com to be a fantastic way to add text and telephony functions to any application. What I did was make it easy to place telephone calls and send text messages using SQL server. I wrote a wrapper to call the Twilio API from a SQL function.
You can download the project free at austinhenderson.com
It takes just a few moments to install it... and then you need to go to Twilio.com and create an account. They will give you $30 credit just to sign up. A text message is 1 cent so you have plenty of room to play here. You have to confirm the number you want to send from with them (so you cannot spam) but other than that you just need to get the token and auth ID from your dashboard at Twilio.com and you will be sending text in no time - regardless of the carrier - PLUS you can even place telephone calls... not sure if you need that or not but you can do it.
Send Text Messages using SQL Server
I also wrote a tutorial on how to use SQL to conduct Telephone Campaigns - sample code is all provided.
Like I said I dont make a dime for you using Twilio and my project is free to use. There is an option to support my cause and the library will work a bit faster but there is nothing to prohibit you from using it as is for free in your needs.
Hope this helps get you started.
February 12, 2012 at 9:07 pm
Ohh yeah - there is always the SMTP relay method. If your numbers do not change carriers and you do not need confirmation of delivery you could always relay through the SMTP domain for the appropriate carrier - here is a good list[/url].
The problem with is that if the number changes carriers or you have no way of knowing the carrier to begin with then this is not a good option. The Twilio method will not have either of these problems plus you can confirm delivery as a bonus.
Thanks
February 12, 2012 at 9:16 pm
Thanks... will work on it this week and post my use case.
February 12, 2012 at 9:29 pm
Super - you are welcome to reach out to me from my site if you have any specific questions that dont fit this forum. Have a great evening.
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply