Solved

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

Posted on 2010-08-23
11
697 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 500 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
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

 
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

Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Using this function 4 53
Consume a webservice via VB in Visual Studio 2015 3 44
Subtract dates in vb.net 6 33
Convert a string to date 4 40
Today I had a very interesting conundrum that had to get solved quickly. Needless to say, it wasn't resolved quickly because when we needed it we were very rushed, but as soon as the conference call was over and I took a step back I saw the correct …
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…
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

732 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