Ozeki 10 SMS Gateway
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 > Service providers guide > SQL Logging > MSSQL table layout
Legal | Privacy | Terms of use | 5779
18.205.109.152 | 92.118.27.157 | Login