Closed

SMS API integration with SQL Server 2008

This project received 5 bids from talented freelancers with an average bid price of $166 USD.

Get free quotes for a project like this
Employer working
Project Budget
N/A
Total Bids
5
Project Description

The solution has 2 components. (this information is also available in the PDF attached. It is much more readable)

First is a SQL Server 2008 stored procedure that sends an SMS using the HTTP API (documentation attached) when there are unsent messages in the “messagesout” table. A trigger is ok for this if necessary, but preference is to run stored proc as a SQL Agent Job, scheduled every minute or two to check the table for new messages to send. System needs to be robust and able to handle large volumes of data. For performance reasons, please do not use any cursors.

Second, a script/web application is needed to receive SMS messages sent over HTTP from the SMS provider and insert messages to database. Details below.

ADDITIONAL FUNCTIONALITY & FEATURES REQUIRED:

1. Record all responses that are generated by the SMS gateway/provider during the send/validation process (success response, or errors received). Update “messagesout” table with status for each message or batch sent. (see “Database Tables” information below) Set ‘Status’ column value = 200 if successful; If error received, record error number in ‘Status’ column. If success response received, will receive a string message from gateway containing JobID:[MobileNumber]:[SMSID]. I would like to record the JobID in the “messagesout” table in the ‘smsjobid’ column; also, record the SMSID in the ‘smsid’ column.

2. Stored proc needs to be able to handle the following:
- Sending a message to a single recipient. Refer to Section “A. Send SMS Message using Gateway”;
- Sending of a message to multiple recipients in one HTTP POST call. Refer to API documentation for “Batch API: Bulk SMS API.”;
- Optional DeliveryDateTime variable (see documentation). This is used if specifying a future date/time for gateway to send message(s). Every message sent from my MS SQL server will not require the use of this variable, but when it is needed, it has to be included in the HTTP call variables.

3. Script/web application to receive incoming text messages sent by SMS provider to my web server ([url removed, login to view] 2.0/Windows 2003/IIS6). HTTP GET is used. Please use C#. Data received should be parsed into columns and inserted into the MS SQL Server table named “messagesin” (see “Database Tables” information below). Refer to API documentation, Section “B. Receive SMS/Text Messages from Gateway.”

DELIVERABLES:

1. SQL Server 2008 stored procedure that sends SMS using HTTP POST. Refer to API documentation, Section “A. Send SMS Message using Gateway.”

2. Script/web application to run on [url removed, login to view] 2.0 web server (Windows 2003/IIS6) to receive and store to database the incoming messages sent by SMS provider.

3. Any documentation about the design, requirements, usage, and/or installation of code delivered.

4. All source code. If any compiled code is delivered, please provide source code as well.


DATABASE TABLES:

Table name: MessagesOut
Messages being sent from SQL server out to SMS Provider.

- msgid: (identity column) - distinguishes outgoing messages from each other
- receiver: phone number(s) of the recipient(s) of the message
- msg: text of the message
- batchname: name of batch, if this is a batch send. (null allowed)
- deliverydatetime: for scheduled date time of outgoing messages if delayed delivery is desired. If messages sent immediately, this is not used. (null allowed)
- status: stores the response from SMS provider/gateway. 200 = success; When error response received, record error number here.
- smsjobid: stores the jobid received from success response from gateway
- smsid: stores the smsid received from success response from gateway
- sentdatetime: datetime when message was sent


Table name: MessagesIn
Messages received from SMS provider, which originated from a mobile user sending a text to a shortcode.

- msgid: (identity column) - distinguishes messages from each other.
- mobilenumber: phone number of the sender of the message.
- operatorid: denotes which service provider connection was used to receive message.
- msg: text of the message.
- smsinboxid: SMS provider’s reference number for all incoming messages.
- received: datetime message was received.
- shortcode: short code the message was sent to.

I think this is very clear, but contact me if you have questions or ideas.

Looking to make some money?

  • Set your budget and the timeframe
  • Outline your proposal
  • Get paid for your work

Hire Freelancers who also bid on this project

    • Forbes
    • The New York Times
    • Time
    • Wall Street Journal
    • Times Online