How to send SMS messages from MS SQL 2016

In this article you can read about how to send SMS messages from MSSQL Server 2016 with the help of Ozeki NG SMS Gateway. Ozeki NG SMS Gateway needs to be downloaded and installed on your computer to provide SMS functionality to your database.

If you have installed Ozeki NG SMS Gateway software on your computer then you can start to setup your SMS system. To send SMS messages from MSSQL Server 2016 you need to do the followings:

  • Create a new user and database in your MSSQL server.
  • Create two tables in the created database for the outgoing and incoming messages: "ozekimessageout" and "ozekimessagein".
  • Install MSSQL native client on the PC where Ozeki NG SMS Gateway is installed.
  • Connect Ozeki NG SMS Gateway and database together.

Create a new user and database in your MSSQL server 2016

Start SQL Server Management Studio at Start / All programs / Microsoft SQL Server 2016 / SQL Server Management Studio. Then log into MSSQL Server with your username and password that you usually use for authentication in Windows. Now click on Security menu item and then right click on New Login menu item. (Figure 1).


Figure 1 - Select new login menu item

Provide a username, select SQL server authentication method and give a password for this user (Figure 2).


Figure 2 - Username and password

Now right click on the Databases menu item in the left menu, and select the New database menu item (Figure 3).


Figure 3 - New database

In the next window you need to provide a name for the database, and you have to specify the user who will be the owner of this database (Figure 4). Select the previously created user (Figure 4).


Figure 4 - Database name and owner

After you have create the database, you need to create two tables. First, click on New Query menu item (Figure 5).


Figure 5 - New Query

Here you need to give the script in order to create the database tables. Ozekimessageout will be responsible for sending out SMS messages, and ozekimessagein will store the incoming SMS messages.(Figure 6).


Figure 6 - Create tables

Database table script:


CREATE TABLE ozekimessagein (
 id int IDENTITY (1,1),
 sender varchar(30),
 receiver varchar(30),
 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(30),
 receiver varchar(30),
 msg nvarchar(160),
 senttime varchar(100),
 receivedtime varchar(100),
 operator varchar(100),
 msgtype varchar(30),
 reference varchar(30),
 status varchar(30),
 errormsg varchar(250)
);

The next step is to install MSSQL Server Native Client to the PC where Ozeki NG SMS Gateway is installed. If you wish to connect your Ozeki NG to the MSSQL server throught OleDB provider, you need to install this plugin.

After you have installed SQLNCLI, return to the GUI of your Ozeki NG SMS Gateway. On the right side of the main page, click on Add new user or application link (Figure 7).


Figure 7 - Add new user or application

Select the Database user from the list of available users and applications (Figure 8).


Figure 8 - Database user

Provide a name for the Database user (Figure 9).


Figure 9 - Name of the database user

The next step is to select a Connection string type and provide the Connection string in order to connect the two software together. In the connection string you need to change the following parameters based on your server: Server (you need to provide the name of the server with IP address or hostname), Database name, Uid and Pwd (Figure 10).


Figure 10 - Connection type and connection string

  • Connection string type: OleDB
  • Connection string: Provider=SQLNCLI11;Server=192.168.113.166\SQLEXPRESS; Database=ozeki;Uid=ozeki;Pwd=Ozeki123;

If you click on Ok button, you can find in the log entries whether the connection has been built succesfully or not (Figure 11).


Figure 11 - Events

If you would like to send a test message from the ozekimessageout table, you only need to click on SQL prompt menu in the left part of the GUI, and select the insert into query that can be found in the dropdown list (Figure 12).


Figure 12 - SQL prompt

If you have inserted a test message into the ozekimessageout table, you can verify if it is sent or not under the Events menu. (Figure 12).


Figure 13 - Message delivery

If you would like to read more about database user, visit the following website: How to send and receive SMS messages from a database application

More information