How to make scripted calls from MS SQL

This article is a detailed guide on how to make scripted calls from MS SQL. You are required to have Ozeki Phone System installed to implement this solution. We are going to create a database and set up all neccessary configurations in Ozeki Phone System, then use this system to make a test call to a mobile device. We hope you will find this article helpful and informative. Now let's get started!

What is MS SQL server?

MSSQL is a suite of database software published by Microsoft SQL itself means Stuctured Query Language, and it is used to run databases. SQL Server is a relational database management system, or RDBMS, developed and marketed by Microsoft. Similar to other RDBMS software, SQL Server is built on top of SQL, a standard programming language for interacting with the relational databases.

What is a scripted call?

A call script, a written script entailing correct wording and logic, assists an agent in handling a contact. Call scripts can be easily integrated with telephone and IVR systems in order to provide the agent useful information about the customer and tailor each interaction accordingly.

What is Ozeki Phone System?

Ozeki Phone System is software for Windows that transforms a computer into a communication server. Ozeki Phone System lets you build applications like PBX, VoIP gateway, IVR and ACD. It can be used for voice calls, video calls, SMS messaging and new-, revolutionary channels like real time communication through webbrowsers and mobile phones.

How to make scripted calls from MS SQL (video tutorial)

In this video tutorial you will leatn how to make scripted calls from MS SQL. For this process you need to install MS SQL and Ozeki Phone System. In this example, we are going to create a database and make a call with this database using Ozeki Phone system. Follow along the steps presented in this video to implement this solution successfully.

SQL Code

// *******************************************
// Create the database 
// *******************************************

CREATE DATABASE ozekidb
GO

sp_addLogin 'ozekiuser', 'ozekipass'
GO
  
sp_addsrvrolemember 'ozekiuser', 'sysadmin'
GO
  
USE ozekidb
GO

// *******************************************
// Create the database table layout
// *******************************************
  
CREATE TABLE ozekicallsin (
id int IDENTITY (1,1),
sender varchar(255),
receiver varchar(255),
msg text,
senttime varchar(100),
receivedtime varchar(100),
operator varchar(30),
reference varchar(30),
);
  
CREATE TABLE ozekicallsout (
id int IDENTITY (1,1),
sender varchar(255),
receiver varchar(255),
msg text,
callid varchar(255),
startime varchar(40),
endtime varchar(40),
status varchar(40),
talkduration varchar(40),
ringduration varchar(40),
operator varchar(100),
errormsg varchar(250)
);
    
CREATE TABLE ozekicallscripts (
id int IDENTITY (1,1),
msg text,
phonenumber varchar(40)
);

GO

// *******************************************
// How to make a call: example 1
// The script is in the ozekicallsout table
// *******************************************

INSERT INTO ozekicallsout (status,receiver,msg)
VALUES ('send','101','
<?xml version="1.0"?>
<Response>
	<Request>
        <OnAnswered>
            <Speak Party="caller">You will get dtmf keys 123.</Speak>
            <SendDTMF Party="caller" Keys="123"></SendDTMF>
            <Hangup/>
        </OnAnswered>
    </Request>
</Response>
');

// *******************************************
// How to make a call: example 2
// The script is in a file on drive C
// *******************************************

INSERT INTO ozekicallsout (status,receiver,msg) VALUES ('send','101','C:\ozml\script.ozml');

// *******************************************
// How to make a call: example 3
// The script is in the ozekicallscripts table
// *******************************************

SET identity_insert ozekicallscripts ON;

INSERT INTO ozekicallscripts (id,msg)
VALUES ('1','
<?xml version="1.0"?>
<Response>
	<Request>
        <OnAnswered>
            <Speak Party="caller">You are using call script 1. Thank you for calling. </Speak>
            <Hangup/>
        </OnAnswered>
    </Request>
</Response>
');

INSERT INTO ozekicallsout (status,receiver,msg) VALUES ('send','101','1');

Download MSSQL database installer

Before settuing up the database itself, you need to download the MSSQL database installer. Open your browser and visit this link here. Scroll down and find the Express version. Click on the blue Download now button (Figure 1). This will begin downloading the installer to your browser's deafult download directory.

download mssql database installer
Figure 1 - Download MSSQL database installer

Setup MSSQL database

After you have downloaded the MSSQL database installer you need to run it (Figure 2). On the inital screen, you will be asked to choose an installation type. Click on Basic, as this will suit our project perfectly.

setup mssql database
Figure 2 - Setup MSSQL database

Installation process

Here you will be asked to accept a lincese agreement. Click on the Accept button in the bottom of the window to do so. Then, specify the directory where you want MSSQL to be installed. Click on the Install button to begin the installation. Lastly, wait for the installation process to complete (Figure 3).

installation process
Figure 3 - Installation process

Connect to database

After the installation process is complete, you can connect to the databas straight away (Figure 4). Click on the blue Connect Now button on the bottom left of the installer. This will open the SQLCMD terminal. Here you will be automatically connected to the database.

connect to database
Figure 4 - Connect to database

Create database and ozekiuser

After you established connection to MSSQL, you can now create a database. The entire SQL code for the project can be found above Copy and paste the piece of code corresponding to Figure 5 into the terminal. Press enter to create the database and a database user complete with login credentials.

create database and ozekiuser
Figure 5 - Create database and ozekiuser

Create ozekicall tables

Now that you have created a database, it's time to add tables. The entire SQL code for the project can be found above Copy and paste the piece of code corresponding to Figure 6 into the terminal. Press enter to create the tables for:

  • Incoming calls (ozekicallsin)
  • Outgoing calls (ozekicallsout)
  • Call scripts (ozekicallscripts)

create ozekicall tables
Figure 6 - Create ozekicall tables

Install service extension

The database is now properly set up. Now you have to switch over to Ozeki Phone System. Click on Extensions inside the menu on the right (Figure 7). Click on the blue Create new Extension button on the top. Click on the Service icon on the right.

install service extension
Figure 7 - Install service extension

Create SQL Call extension

Here you need to select which type of call extension you want to create. Select SQL Call by clicking on it (Figure 8).

create sql call extension
Figure 8 - Create SQL Call extension

Install MSSQL call extension

Find MS SQL Server Calls and click on Install next to it (Figure 9).

install mssql call extension
Figure 9 - Install MSSQL call extension

Setup connection details

After you have selected MS SQL Server Calls, you must configure your new connection. This is done in the Connection settings section of Extension details (Figure 10). Here you need to provide the following details by entering them into the corresponding textboxes:

  • Ip Address (next to Server)
  • Port
  • Database
  • UserID
  • Password
Click on OK to create the connection.

setup connection details
Figure 10 - Setup connection details

Connect to MSSQL database

You have now created your connection. Enable your connection by clicking on the swtich button on the right (Figure 11). The switch turns green to indicate that a connection is active.

connect to mssql database
Figure 11 - Connect to MSSQL database

Create new outside line connection

After you have successfully created the database connection, you have to create a new outside line connection. Click on Outside lines inside the menu on the left (Figure 12). Click on the blue Create new Phone Line button on the top. In the Phone line details menu on the right, click on VoIP PBX. This will bring you to a configuration menu.

create new outside line connection
Figure 12 - Create new outside line connection

Setup outside line connection

In this menu on the right side of the screen, you need to configure your outside line connection (Figure 13). Here you need to provide the following details by entering them into the corresponding textboxes:

  • Display name
  • Username
  • Register name
  • Password
  • Domain (IP address)
  • Port
Click on OK to create the connection.

setup outside line connection
Figure 13 - Setup outside line connection

Create dial plan rule

The last step before you are able to use the database, is to create a dial plan rule. Click on Dial plan inside the menu on the left (Figure 14). Click on the blue Create new Route button on the top. Look for the Settings section in the Route details menu on the right. Set From: as 9000. Set To: as voip_Phone_Line_1.

create dial plan rule
Figure 14 - Create dial plan rule

Insert call into database

You are now able to use the databas for scripting calls. Open the details page of your MSSQL connection. Click on the SQL tab to open the SQL text editor. Paste the code shown in Figure 15. Click on Execute to run the code, and insert the call into the database.

insert call into database
Figure 15 - Insert call into database

Phone called from database

Here you can see that the phone is being called from the database (Figure 16).

phone called from database
Figure 16 - Phone called from database

Summary

This is the end of the tutorial. You are now able to send SMS using MSSQL and Ozeki Phone System. We hope you found the information presented in this article helpful. For more information on Ozeki Phone System and our various different solutions, check out our other guides too.

More information