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

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?

LVL 10
GlobaLevelProgrammerAsked:
Who is Participating?
 
Norman MainaConnect With a Mentor Commented:
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
 
GlobaLevelProgrammerAuthor Commented:
is this from an article...where would I find the link?
0
 
Norman MainaCommented:
0
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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


0
 
GlobaLevelProgrammerAuthor Commented:
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
 
GlobaLevelProgrammerAuthor Commented:
AFAIK

...no idea what that is...
0
 
dqmqCommented:
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
 
GlobaLevelProgrammerAuthor Commented:
what about recieving sms??
0
 
dqmqCommented:
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
 
GlobaLevelProgrammerAuthor Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.