Welcome page
Products
Ozeki 10
Apps
Chat
SMS Gateway
SMS with Client Software
HTTP API
SMS from/to Database
MSSQL Express
MSSQL Express Installation
Create Database
Configure Database User
Test Message
Oracle SMS
MySQL SMS
PostgreSQL
SAP SQL Anywhere
Microsoft Access
PHP SMS API
Solutions
C# SMS API
VB.NET SMS API
SQL Logging
System Database
Robot Controller
Softphone
Connections
Installation guide
Terms and Definitions
SMS
Bulk Messenger
Cluster
Developer tools
Robots
Hardware
Technology
Company


Ozeki SMS Gateway
SMS from/to Database SMS from/to Database | MSSQL Express Installation MSSQL Express Installation

SMS from/to MSSQL Express

You can use Microsoft SQL Express database server for SMS messaging. 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.

How to send SMS from MSSQL Express
MSSQL Express Installation (Video guide)
Create Database Tables (Video guide)
Configure Database User (Video guide)
Send Test Message (Video guide)

This page extends the general guide to SMS from/to Database.

To use this messaging function, please install and configure Ozeki SMS Gateway. After software configuration, please open it's 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 and database connection string as seen in the examples on this page. In SQL Express you should provide the following parameters:

Connection type:

OLE DB

It is an API designed by Microsoft, allows accessing data from a variety of sources.

Connection string:

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

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.

C:\sqlcmd -S .\SQLExpress

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(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)
);

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://www.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={SQL Server};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 2008

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

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


Copyright © 2000- - Ozeki Ltd | info@ozeki.hu | Tel: +36 1 371 0150
Home > Products > Ozeki 10 > Apps > SMS Gateway > SMS from/to Database > MSSQL Express
Legal | Privacy policy | Terms of use
Page: 5706 | Login | 52.91.245.237