High performance database reporting settings for MS SQL

This guide explains how to setup a high-performance database reporting for Microsoft SQL Database server to save all SMS messages going through the system. This setup makes it possible for you to create reports, write bills or to calculate the cost of operating your SMS gateway system.

The reporting system can save messages going through the system into different sources, such as databases, txt files, etc. One of the most popular choices is using MS SQL server to save a log of each message. You are able to open reporting system from the Edit menu Reporting option as you can see it on the Figure 1.

Figure 1 - Open Reporting menu

In the Reporting menu click on the Create new SMS Reporter button and then select MS SQL Fast type database (Figure 2).

Figure 2 - Create new MS SQL Fast reporter

Now enter the connection details like Server, Port, Database, UserID and Password in the details page of the MS SQL Fast reporter. And enter the Connection name. Finally click on the OK button (Figure 3).

Figure 3 - Specify connection details

In the Events tab you can see the logging of the latest server events. As you can see on the Figure 4 the database connection is established.

Figure 4 - Database connection established

As Figure 5 shows on the events tab, you can see that the SMS Gateway automatically creates the appropriate database tables. Table "m" stores messages and "c" stores contacts.

In the "m" table you can see the below columns:

  • fc: from connection
  • fa: from address
  • tc: to connection
  • ta: to address
  • m: message
  • dc: created date
  • ds: submitted date
  • dd: delivered date
  • r: submit reference
  • e: error message
  • c: pdu count
  • s: status code

Status code should be the follow:

  • 1 - submit success
  • 2 - submit failed
  • 3 - delivered
  • 4 - delivery failed

Figure 5 - Tables created

Finally in the MS SQL Server you will see there is a new message in the table as you can see in the Figure 6.

Figure 6 - Message inserted into database

More information