?
Solved

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

Posted on 2010-08-23
11
Medium Priority
?
703 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 3
  • 3
11 Comments
 
LVL 12

Accepted Solution

by:
NormanMaina earned 2000 total points
ID: 33503302
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
ID: 33503319
is this from an article...where would I find the link?
0
 
LVL 12

Expert Comment

by:NormanMaina
ID: 33503353
0
Put Machine Learning to Work--Protect Your Clients

Machine learning means Smarter Cybersecurity™ Solutions.
As technology continues to advance, managing and analyzing massive data sets just can’t be accomplished by humans alone. It requires huge amounts of memory and storage, as well as high-speed processing of the cloud.

 
LVL 42

Expert Comment

by:dqmq
ID: 33503358
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
ID: 33503370
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
 
LVL 10

Author Comment

by:GlobaLevel
ID: 33503392
AFAIK

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

Expert Comment

by:dqmq
ID: 33503400
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
ID: 33503413
0
 
LVL 10

Author Comment

by:GlobaLevel
ID: 33505496
what about recieving sms??
0
 
LVL 42

Expert Comment

by:dqmq
ID: 33505907
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
ID: 33623914
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

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

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…
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
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 is my first video review of Microsoft Bookings, I will be doing a part two with a bit more information, but wanted to get this out to you folks.

752 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