Learn how to send and receive SMS messages using a Microsoft SQL Server (Express) database with Diafaan SMS Server. Let Diafaan SMS Server create the database tables or create the database tables manually with an SQL script.
Microsoft SQL Server
Microsoft SQL Server is Microsoft's high performance SQL Server relational database management system. Drivers to access the database are built-in in Microsoft Windows so it is not necessary to install any additional drivers to use the database.
Microsoft SQL Server Express
Microsoft SQL Server Express is a freely-downloadable and distributable version of Microsoft's SQL Server relational database management system. For all intent and purposes it is compatible with the regular version of Microsoft SQL Server and uses the same drivers. The only practical difference is that \SQLEXPRESS should be added to the data source (server) name in the connection string.
Create the SMSServer database
You can use the setup wizard to create the database tables and fields for the MessageOut, MessageIn and MessageLog tables.
The setup wizard creates a database with the following table and field layout:
CREATE DATABASE SMSServer;
CREATE TABLE MessageOut ( Id int IDENTITY (1,1) PRIMARY KEY, MessageTo nvarchar(80), MessageFrom nvarchar(80), MessageText nvarchar(max), MessageType nvarchar(80), MessageGuid nvarchar(max), MessageInfo nvarchar(max), Gateway nvarchar(80), UserId nvarchar(80), UserInfo nvarchar(max), Priority int, Scheduled datetime, ValidityPeriod int, TLVList nvarchar(max), IsSent bit NOT NULL DEFAULT 0, IsRead bit NOT NULL DEFAULT 0);
CREATE INDEX IDX_IsRead ON MessageOut (IsRead);
CREATE TABLE MessageIn ( Id int IDENTITY (1,1) PRIMARY KEY, SendTime datetime NOT NULL DEFAULT getdate(), ReceiveTime datetime, MessageFrom nvarchar(80), MessageTo nvarchar(80), SMSC nvarchar(80), MessageText nvarchar(max), MessageType nvarchar(80), MessageParts int, MessagePDU nvarchar(max), Gateway nvarchar(80), UserId nvarchar(80));
CREATE TABLE MessageLog ( Id int IDENTITY (1,1) PRIMARY KEY, SendTime datetime NOT NULL DEFAULT getdate(), ReceiveTime datetime, StatusCode int, StatusText nvarchar(80), MessageTo nvarchar(80), MessageFrom nvarchar(80), MessageText nvarchar(max), MessageType nvarchar(80), MessageId nvarchar(80), MessageGuid nvarchar(max), MessageInfo nvarchar(max), ErrorCode nvarchar(80), ErrorText nvarchar(max), Gateway nvarchar(80), MessageParts int, MessagePDU nvarchar(max), Connector nvarchar(80), UserId nvarchar(80), UserInfo nvarchar(max));
CREATE INDEX IDX_MessageId ON MessageLog (MessageId, SendTime); |