SMS from/to MSSQL

You can use Microsoft SQL Server or Microsoft SQL Express for sending and receiving SMS. Follow this page to send or receive SMS messages through Ozeki SMS Gateway's Database User. All you have to do is insert or read data rows from the appropriate SQL table by setting INSERT INTO and SELECT statements. The solution uses Ozeki SMS Gateway installed on your PC. This solution is divided into 4 sections:

What is MSSQL?

Microsoft SQL Server is a relational database management system developed by Microsoft. As a database server, it is a software product with the primary function of storing and retrieving data as requested by other software applications, which may run either on the same computer or on another computer across a network (including the Internet). Microsoft markets at least a dozen different editions of Microsoft SQL Server, aimed at different audiences and for workloads ranging from small single-machine applications to large Internet-facing applications with many concurrent users.

What is MSSQL Express?

SQL Server Express, Microsoft’s free database edition, can be utilized in the Azure Cloud by installing on an Azure Virtual Machine (VM) – for developing small server-driven applications. It can also be utilized for any production database that is sized at or below the current SQL. In this case it is used to send SMS through an SMPP client connection.

What is Ozeki SMS Gateway?

Ozeki SMS Gateway is a communication software that routes messages between real world entities. It allows you to build up simple communication between shared hardware and software resources of your system. It offers real time information and provides various configuration options to route messages.

MSSQL Installation
Create the database tables for SMS sending and receiving
Setup the MSSQL connection in Ozeki
Send a test SMS message

Please make sure you read the general SQL to SMS how to before continuing.

How to send SMS from MS SQL Express (Quick steps)

To send SMS from MS SQL Express:

  1. Create database and tables
  2. Launch the Ozeki SMS Gateway app
  3. Add new SQL messaging application
  4. Install MS SQL Express
  5. Configure Connection settings
  6. Enable MS SQL Express connection
  7. Insert a text message into the database
  8. Check the SMS message in the Sent folder

How to receive SMS with MS SQL Express database

This SMS receiving system makes it possible to receive SMS using an MS SQL Express Database. This system consists of a phone that will be used to send messages, a computer with Ozeki SMS Gateway installed, a database server, and your application. The procedure works the following way: The phone sends the message. The computer with the Ozeki SMS Gateway will store the message in an MS SQL Express Database Server. Your application could get the messages using an SQL Query. (Figure 1)

how to receive sms with mssql express database
Figure 1 - How to receive SMS with MS SQL Express database

How to send SMS from MS SQL Express database

This SMS sending system makes it possible to send SMS using an MS SQL Express database. This system consists of a phone that will be used to receive the messages, a computer with Ozeki SMS Gateway installed, a database server, and your application. The procedure works the following way: Your application will send the message to the MS SQL Express Database Server. It will store and log your message, so the Ozeki SMS Gateway will be able to acquire it using an SQL Query. The message will be sent to the phone after it was acquired. (Figure 2)

how to send sms with mssql express database
Figure 2 - How to send SMS with MS SQL Express database

How to send SMS from MS SQL Express database (video tutorial)

In this quick video tutorial, you will learn how to send SMS from MS SQL Express database. First, we need to create the databases themselves in the Command Prompt. Then, we add an MS SQL Express messaging interface to Ozeki SMS Gateway. Lastly, we can use this connection to send SMS using the previously created databases.

Video 1 - How to send SMS from MS SQL Express database (Video tutorial)

Create database and tables

To use this messaging function, please install and configure Ozeki SMS Gateway. After software configuration, please open its management consol so you can install a database user. You will be able to send and receive SMS messages with an SQL Express database server. It is required to add the database connection type as seen in the examples on this page. In SQL Express you should provide the parameters you can see on Figure 3:

create database and tables
Figure 3 - Create database and tables

Select 'Add new user or application'

First you need to be on the Home page of Ozeki SMS Gateway. Navigate to the right-hand side of the screen to Users and applications. Click on Add new user/application highlighted in blue (Figure 4). This will take you to a new screen where you can configure your new user.

select add new user or application
Figure 4 - Select 'Add new user or application'

Install SQL Messaging User

Under Application interfaces, you need to find SQL messaging. Click on the blue Install button (Figure 5). You will then be taken to another screen where you can select what type of SQL messaging interface you wish to create.

install sql messaging user
Figure 5 - Install SQL Messaging User

Install MS SQL Express connection

Find MS SQL Express among SQL messaging interfaces. Click on the blue Install next to it again (Figure 6). This will forward you to a configuration screen where you are able to set up your new MS SQL Express connection.

install ms sql express connection
Figure 6 - Install MS SQL Express connection

Define the MS SQL Express database connection details

The next step is to provide the details for your database connection (Figure 7). First you must type in the path of your Server along with the Port. Then you need to type in the name of your previously created database. Lastly, you must provide a username and a password for this connection. Click on OK to complete the configuration.

define the ms sql express database connection details
Figure 7 - Define the MS SQL Express database connection details

Enable MS SQL Express connection

With your connection successfully created, you may now enable it. Turn the connection on by clicking on the switch button on the left (Figure 8). The switch turns green to indicate that it has been turned on. Now everything is ready to use the MS SQL Express connection for sending SMS.

enable ms sql express connection
Figure 8 - Enable MS SQL Express connection

Insert message to the database table

Start by going into the SQL tab of MS SQL Express connection. You need to insert the message into the database by typing this piece of code into the text editor (Figure 9). Click on the Execute button below to run the code and insert the message into the database.

insert message to the database table
Figure 9 - Insert message to the database table

SMS message sent

After executing the SQL code, navigate to the Sent folder (Figure 10). This folder is found inside a menu on the left. Inside this folder you will find that the SMS message has been successfully sent.

sms message sent
Figure 10 - SMS message sent

Congratulation for configuring a database user! Now you should create a database layout by using SQL Express. Receiving and sending text messages will work on separate SQL tables ('ozekimessagein' and 'ozekimessageout'). You can control these tables by using the INSERT and SELECT statements. To create a database layout, please open a windows command prompt and run 'cmd.exe' to start the SQL Command interpreter.

sqlcmd
SQL Express will start running, so you can type your statements in the command interpreter. If the command interpreter cannot connect to SQL Express, please read How to connect to SQL Express using the SQL command line utility. Please type the following statements to create tables and add 'sysadmin' rights to your database user (Figure 1). Although you can give any table name you wish. 

CREATE DATABASE ozeki
GO

USE ozeki
GO

CREATE TABLE ozekimessagein (
 id int IDENTITY (1,1),
 sender varchar(255),
 receiver varchar(255),
 msg nvarchar(160),
 senttime varchar(100),
 receivedtime varchar(100),
 operator varchar(30),
 msgtype varchar(30),
 reference varchar(30),
);

CREATE TABLE ozekimessageout (
 id int IDENTITY (1,1),
 sender varchar(255),
 receiver varchar(255),
 msg nvarchar(160),
 senttime varchar(100),
 receivedtime varchar(100),
 operator varchar(100),
 msgtype varchar(30),
 reference varchar(30),
 status varchar(30),
 errormsg varchar(250)
);

GO

sp_addLogin 'ozekiuser', 'ozekipass'
GO

sp_addsrvrolemember 'ozekiuser', 'sysadmin'
GO
Figure 1 - CREATE TABLE statement. It also adds rights to 'ozekiuser'

Later on, you can use the command prompt to modify the size or data type of the SMS messages.

Now you are ready to send your first SMS message by inserting a record into 'ozekimessageout'.
Use the following SQL statement example:

insert into ozekimessageout (receiver,msg,status) values ("+44111223465","Hello
 world","Send");
GO

Tip: SQL Express is free and can be downloaded from the following URL:
https://microsoft.com/en-us/sql-server/sql-server-editions-express

Trouble shooting

In some systems it is harder to connect to SQL Express. If this is the case, you can see the following error message:
ERROR 6001: Database connection error: The 'SQLNCLI' provider is not registered on the local machine..

SQLNCLI should be installed with SQL Express. This error shows that it is not installed on your machine. Please try to change the provider to 'sqloledb', so you can use OLE DB. By changing the provider the connections string changes as well:

Connection type:

OLE DB

Connection string:

Provider=SQLOLEDB;Data Source=.\SQLEXPRESS;User ID=ozekiuser;
password=ozekipass;Database=ozeki;Persist Security Info=True

Or change the whole connection type to: 

ODBC

and use the following connection string: 

Driver={SQLServer};Server=.\SQLEXPRESS;User ID=ozekiuser;
password=ozekipass;Database=ozeki;Persist Security Info=True;

Or change the whole connection type to: 

SQLServer

and use the following connection string: 

Server=.\SQLEXPRESS;User ID=ozekiuser;password=ozekipass;
Database=ozeki;Persist Security Info=True;

One of the 3 strings above should fix this ERROR.

Microsoft SQL Server 2019

Try the following string if you need to connect to Microsoft SQL Server 2019:

Provider=SQLOLEDB.1;Data Source=YourHostName;Persist Security 
Info=False;Integrated Security=SSPI;User ID=UserName;
Password=User'sPassword;Initial Catalog=DatabaseName

How to test sms sending with your SQL server

If you wish to test SMS functionality without actually connecting to the mobile network, you have two options: You can use the built in sms tester connection or you can setup two sms gateways to have a more realistic testing environment. The first option is an excellent choice for performance testing. The second option is better because it allows you to test all aspects of mobile messaging.

If you setup two sms gateways, you need to configure the first SMS gateway as an SMPP simulator. This SMS gateway will provide SMPP server service to the second SMS gateway. It will operate the same way the SMSC of a mobile network operator would operate. The second SMS gateway will connect to the first using an SMPP client connection, and it will provide SQL to SMS service to your applications.

Summary

Thank you for sticking with us by the end of this article. Hopefully this tutorial was helpful and informative in bringing you closer to using Ozeki SMS Gateway and MS SQL. To learn more, check out our other tutorials, videos and guides on various other topics. Ozeki SMS Gateway offers many more solutions to different tasks and processes.

More information