Solved

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

Posted on 2010-08-23
11
695 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
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
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

 
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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Help with exporting to excel 4 36
VB.Net - MemoryMappedFiles - Confirm receipt 2 24
Powershell File Sort 8 41
Upgrade code from VS 2010 to VS 2015 7 16
Data architecture is an important aspect in Software as a Service (SaaS) delivery model. This article is a study on the database of a single-tenant application that could be extended to support multiple tenants. The application is web-based develope…
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…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

828 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