Products
Ozeki 10
Apps
Chat
SMS Gateway
Quick Start Guide
SMS with Client Software
HTTP API
SMS from/to Database
PHP SMS API
Solutions
C# SMS API
VB.NET SMS API
SQL Logging
MySQL table layout
Oracle table layout
MSSQL table layout
SQL templates
SQL Log Tracking
Keywords
Advanced Traffic Queries
System Database
Robot Controller
Softphone
PBX
Connections
Installation guide
Terms and Definitions
HTTP API
SMS
Bulk Messenger
Cluster
Developer tools
Robots
Hardware
Installer
Technology
Company


Ozeki 10 - SMS Gateway server software
Oracle table layout Oracle table layout | SQL templates SQL templates

MSSQL table layout

Type connection information and create the table structure in your Microsoft SQL database layout, so you can run SQL logging statements on your database, which makes you capable to track all outgoing and incoming messages, regardless of the sender's or receiver's address.

Connection Type:SQLNCLI (Microsoft SQL Server connection string)
Connection String:Provider=SQLNCLI;Server=YourServer;Database=YourDatabase;MultipleActiveResultSets=True;
UID=YourUsername;PWD=YourPassword;
Figure 1 - Modify this connection string to fit your own SQLNCLI driver connection

On your Microsoft SQL database server, please CREATE exactly two tables both for incoming or outgoing message logs. In this example these are called 'inbox' and 'outbox' tables to store traffic information (Figure 2).

Microsoft SQL Server create table layout
USE ozeki; // Change 'ozeki' to the name of the database scheme you wish to use
GO
CREATE TABLE  outbox (
id int IDENTITY(1,1),
username varchar(30),
msgtype varchar(160),
msgid varchar(60),
callbackid varchar(255),
sender varchar(30),
receiver varchar(30),
msgsubject varchar(1024),
msgdata varchar(1024),
acceptedfordeliverytime datetime,
deliveredtonetworktime datetime,
deliveredtohandsettime datetime,
operator varchar(30),
route varchar(30),
status varchar(130),
errormessage varchar(1024),
cost varchar(10)
);
GO  
ALTER TABLE dbo.outbox ADD CONSTRAINT PK_outbox_id PRIMARY KEY (id);
GO
CREATE INDEX Index_outbox_msgid ON dbo.outbox (msgid);
GO

CREATE TABLE  inbox (
id int IDENTITY(1,1),
username varchar(30),
msgtype varchar(160),
msgid varchar(60),
sender varchar(30),
receiver varchar(30),
msgsubject varchar(1024),
msgdata varchar(1024),
senttime datetime,
receivedtime datetime,
operator varchar(30)
);
GO
ALTER TABLE dbo.inbox ADD CONSTRAINT PK_inbox_id PRIMARY KEY (id);
GO
CREATE INDEX Index_inbox_msgid ON dbo.inbox (msgid);
GO
Figure 2 - Copy CREATE TABLE statements to create this table structure

Ozeki SMS Gateway uses SQL statements to manipulate database table.


Copyright © 2000- - Ozeki Ltd | info@ozeki.hu
Home > Products > Ozeki 10 > Apps > SMS Gateway > SQL Logging > MSSQL table layout
Legal | Privacy policy | Terms of use
Page: 5779 | Login | 3.95.131.97