MSSQL Database layout for Ozeki 10 softwares

Having a software that can send and receive SMS message can revolutionize your information transfer, especially mass communication. You could send out messages to multiple people at once, or even delay them. You will see, that it only requires two tables to be created, one for the incoming and one for the outgoing messages. This guide shows you how to create them using sqlcmd.

Content
1. Connect to MSSQL Express 2. Copy CREATE TABLE statement 3. Run CREATE TABLE statement

Please scroll down to copy the CREATE TABLE statements used in the video. If you have created the database in MSSQL Express, you can jump to the next video.

We will use the Windows Command Prompt to create the tables. It means that you have to be able to open sqlcmd. It is a mode, which is for creating, testing and executing SQLCMD commands or scripts. It will be installed with SQL Server. To write in sqlcmd, please run cmd on your computer and type in sqlcmd. After pressing enter, you will be able to use sqlcmd. You can se sqlcmd in work on Figure 1.

connect to the microsoft sql express database
Figure 1 - Connect to the Microsoft SQL Express database

Here, we present you the code you need to run in the sqlcmd mode, to have two tables that can work with Ozeki SMS Gateway. With this code, you can give the sqlcmd statements, which will make it create one table for the incoming and one for the outgoing tables, set the login credentials, and set the rights for the user. You can see this code on Figure 3. 


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 2 - CREATE TABLE statement

To have the code in the sqlcmd, first you need to copy it. You can see on Figure 3, that all you need to do is highlight the code on Figure 2, right click the highlighted text, and click copy. This way, you could have it in the clipboard, and paste it to anywhere.

Figure 3 - Copy CREATE TABLE statement from Figure 2

Now please paste the copied code form the previous step, into the sqlcmd which you created before. This way the program can understand and execute every statement and create the two required tables. Press enter to run the code and create the tables. You can see the code inside the sqlcmd on Figure 4.

Figure 4 - Run CREATE TABLE statement on the database

We hope that this guide was helpful. If you have any problem with any of the steps, feel free to contact us at info @ ozeki.hu

More information