Closed

SMS API integration with SQL Server 2008

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.

Skills: Graphic Design, HTML, Logo Design, PSD to HTML, Website Design

See more: sms sql server 2008, sms integration sql server, sql server 2008 sms, sms integration sql, sms sql integration, sql sms message, sms api sql server, send sms sql server, website design procedure, two column web design, success design, string b, sql server web, sql get date, sql code pdf, robust design, receiver update, procedure design website, performance requirements mobile application, mobile application integration, job application response, graphic design service post, delivery agent, clear string, check call system

About the Employer:
( 0 reviews ) Lahore, Pakistan

Project ID: #709279

5 freelancers are bidding on average $166 for this job

asivia

Please check PMB for our portfolio. Thanks

$250 USD in 7 days
(0 Reviews)
0.0
afrinahossain

Dear Sir I have been eagerly waiting to land my first job. This time i have got a chance to start my outsourcing career with you. If you let me do this job then i will be able to make you satisfied. Since,this is my f More

$50 USD in 7 days
(0 Reviews)
0.0
yourprofs

Dear Sir/Ms, 8 years’ experience can assure you that we provide the professional service to meet your needs. I will send you some of our cases in the PM. Thank you for reading Terry

$200 USD in 5 days
(1 Review)
0.0
grigarsh

Hi, i am interested in the job and have an experience in developing tools working with SMS

$180 USD in 10 days
(0 Reviews)
0.0
kchowdary149

Hello, I have read your requirements, understand what you need and can provide my services for this job. I have flare of programming using new techniques, so please give me a chance to prove my worth. I am very More

$150 USD in 6 days
(1 Review)
0.0