Solved

ms sql 2005 - use db_mail to send recieve SMS text msgs

Posted on 2010-08-23
11
686 Views
Last Modified: 2013-11-10
Is it possible to microsft Sql server to send and recieve sms text messages...running against a table in sql to send out texts messages as the table is filled.

Then if their are incoming to write it to another table...

I dont know if .net has this functionality to send sms over http...

are there any examples? any exisitng code?

0
Comment
Question by:GlobaLevel
  • 5
  • 3
  • 3
11 Comments
 
LVL 12

Accepted Solution

by:
NormanMaina earned 500 total points
Comment Utility
How to Notify User via SMS from SQL trigger (send EMail/Text Message):
You can send SMS messages using a SQL Server database server with the help of SQL queries. It is interesting that often, without any external application, you can create powerful SMS enabled solutions. You can do this with the help of database triggers or stored procedures. With this approach you can easily set up SMS to e-mail gateway.
        
For this article, test table was created:

      CREATE TABLE [dbo].[TestTriggerSMS]
      (
            [test_field] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
      ) ON [PRIMARY]
      


Side Note:

    Trrigers - are a special class of stored procedure defined to execute automatically when an UPDATE, INSERT, or DELETE statement is issued against a table or view.
    Triggers are powerful tools that sites can use to enforce their business rules automatically when data is modified.
    Triggers can extend the integrity checking logic of SQL Server constraints, defaults, and rules, although constraints and defaults should be used instead whenever they provide all the needed functionality.
    Tables can have multiple triggers.
    The CREATE TRIGGER statement can be defined with the FOR UPDATE, FOR INSERT, or FOR DELETE clauses to target a trigger to a specific class of data modification actions. When FOR UPDATE is specified, the IF UPDATE (column_name) clause can be used to target a trigger to updates affecting a particular column.


In this article, FOR UPDATE trigger is used:

      create trigger tr_Update_TestTriggerSMS on [dbo].[TestTriggerSMS]
      for update
      as
      set nocount on

      declare @rows int
      select @rows = count(*) from inserted

      -- send notification if TestTriggerSMS table is being updated
            
      if @rows=1      -- notification only if update 1 row
      begin      
            if update(test_field)      -- notification only if update [test_field] field
            begin
                  print ' [test_field] field updated'
                  declare @old_value varchar(50)
                        , @new_value varchar(50)

                  declare @tab char(1)
                  set @tab = char(9)


                  declare @message varchar(555)

                  set @old_value = (select test_field from deleted)
                  set @new_value  = (select test_field from inserted)

                  set @message = 'TestTriggerSMS updated:' + char(13) +
                                    'Old Value ' + @tab  +  @tab + '[' + @old_value + ']'+ char(13) +
                                    'New Value ' + @tab  +  @tab + '[' + @new_value + ']'
                                    
                  exec master.dbo.xp_sendmail @recipients = 'siccolo_mobile_management@yahoo.com'
                        , @message = @message
                        , @subject = 'TestTriggerSMS updated!'

                  -- send SMS to cell phone --
                  /*
                  Teleflip.com now provides SMS service.
                  To use teleflip just email the SMS message to the following email address:
                  <10 digit cell number>@teleflip.com
                        -- or --
                  T-Mobile: phonenumber@tmomail.net
                  Virgin Mobile: phonenumber@vmobl.com
                  Cingular: phonenumber@cingularme.com
                  Sprint: phonenumber@messaging.sprintpcs.com
                  Verizon: phonenumber@vtext.com
                  Nextel: phonenumber@messaging.nextel.com
                  
                  where phonenumber = your 10 digit phone number
                  */
                  exec master.dbo.xp_sendmail @recipients = '4108441212@cingularme.com'
                        , @message = @message
                        , @subject = 'TestTriggerSMS updated!'
            end
      end

      set nocount off
      
0
 
LVL 10

Author Comment

by:GlobaLevel
Comment Utility
is this from an article...where would I find the link?
0
 
LVL 12

Expert Comment

by:NormanMaina
Comment Utility
0
 
LVL 42

Expert Comment

by:dqmq
Comment Utility
db_mail can send SMS messages, no problem.  AFAIK it has nothing to do witn .NET or http


0
 
LVL 10

Author Comment

by:GlobaLevel
Comment Utility
if you dont know the number and put the same number on each carrier..there would be alot of network impact in the sense of return error messages..any way aroung this..is this a problem?
 so for example:
send SMS to 2124449999

2124449999@tmomail.net
2124449999@vmobl.com
2124449999@cingularme.com
2124449999@messaging.sprintpcs.com
2124449999@vtext.com

turns out to be a tmobile number...went thru...however...the other 4 carriers are throwing errors and causing a performance issue..

0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 10

Author Comment

by:GlobaLevel
Comment Utility
AFAIK

...no idea what that is...
0
 
LVL 42

Expert Comment

by:dqmq
Comment Utility
the trick is to use TEXT format, keep the body <= 160 characters, and make sure the recipient is an SMS handler for the appropriate phone provider.



EXEC @return_value = msdb.dbo.sp_send_dbmail
       @profile_name =      'Yourprofile'
      ,@recipients      =      @RecipientList
      ,@subject            =      @Subject
      ,@body       =      @Body
      ,@body_format=      'TEXT'
0
 
LVL 12

Expert Comment

by:NormanMaina
Comment Utility
0
 
LVL 10

Author Comment

by:GlobaLevel
Comment Utility
what about recieving sms??
0
 
LVL 42

Expert Comment

by:dqmq
Comment Utility
You cannot use database mail to receive email.

Your best bet is interface with an email server or an email client that pulls from an email server.  You might also consider 3rd party utility that pushes or syncs email between thedbms and the email server.
0
 
LVL 10

Author Comment

by:GlobaLevel
Comment Utility
I was away for a few weeks...do you have any vba code that will write incoming emails from exchange to a database ms sql? Thanks
0

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Join & Write a Comment

For those of you who don't follow the news, or just happen to live under rocks, Microsoft Research released a beta SDK (http://www.microsoft.com/en-us/download/details.aspx?id=27876) for the Xbox 360 Kinect. If you don't know what a Kinect is (http:…
These instructions are based on installing Owncloud on your new raspberry pi connected with a usb HDD. What do you need Part A? A Raspberry Pi, model B. A boot SD card for the Raspberry Pi. A usb HDD An Ethernet cable to connect to the lo…
The purpose of this video is to demonstrate how to set up the WordPress backend so that each page automatically generates a Mailchimp signup form in the sidebar. This will be demonstrated using a Windows 8 PC. Tools Used are Photoshop, Awesome…
This tutorial demonstrates a quick way of adding group price to multiple Magento products.

728 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now