Call Routing - Database Lookup

Performing a database lookup to determine the target destination is the most common use of the call routing engine. To perform a database lookup, the following properties of the rule must be set:

Select 'Database Lookup'
Connection String -> Set to be a connection string for an ODBC compliant database*
Stored Procedure -> Configure to be the name of the stored procedure to call on the SQL Server database

Routing Flow

The diagram below shows how the call routing should be configured when using a DB lookup rule. The monitored extension should be added to a UCD hunt group so that calls are presented to it one at a time. For each call that alerts the monitored extension, the call routing system will perform a DB lookup and either transfer the call to the resulting target or the overflow configured against the rule.

Connection String

An ODBC connection string is required to tell the routing engine how to connect to the database. Below are some examples of ODBC connection strings for commonly used databases:

SQL Server

The connection string below is an example connection string for an SQL Server database. The database, server, username and password properties must all be updated in the connection string to match those of the target database.

“Driver=[Driver];Server=[server];Database=[database];Uid=[username];Pwd=[password];”

Replace [Driver] with your specific driver name etc. If connecting to a specific SQL Server instance, it must be passed with the server property: servername\instancename.

For help in determining what the connection string should be this web page can be a useful reference (this is an external link, Mitel are not responsible for its contents): http://www.connectionstrings.com

Any ODBC drivers required for the database connection must be installed on the MCS sever manually.

Stored Procedure

The routing engine requires a specific stored procedure to be defined on the database it is connecting to.

The stored procedure must have the following parameter definition:

CREATE PROCEDURE [dbo].[ENTER_STORED_PROCEDURE_NAME_HERE]

(

@cli as varchar(50),

@ddi as varchar(50),

@routetodevice as varchar(50) output,

@alphatag as varchar(50) output,

@accountcode as varchar(50) output,

@mailbox as varchar(50) output,

@cpnumber as varchar(50) output,

@cpname as varchar(50) output

)

The @cli and @ddi parameters will be populated with the details of the call to be routed. If the call is internal, the @ddi parameter will be a zero length string.

The output parameters will then be used by the routing engine to route the call.

If the caller id is withheld, the @cli property will be empty. The stored procedure will need to handle this.

Route to Device

The @routetodevice can be one of the following:

A device on the telephone system (extension, hunt group, CRA etc.)
An agent on the telephone system
An external number

To route the call to the overflow, pass back a null or zero length @routetodevice from the stored procedure.

To route to an agent, enable the 'Agent Return Value' options or prefix the return value with an 'A'.

When routing to an agent, the system will check that the agent is logged in and is available to take a call. If not, the call will be routed to the overflow.

If the @routetodevice is an external number (any number longer than the 'Max Extension Length' configured in the dial plan will be treated as external), the Calling Party Number (@cpnumber) and Calling Party Name (@cpname) for the external call can be set.

Only set the @cpname property if the trunk lines support it. Setting it when they don’t will cause the routing of the call to fail.

Account Code

If the @accountcode property is set, the value will be entered on the call and will override any account code statically configured on the rule.

Account Codes can only be set on external calls.

Alpha Tag

If the @alphatag output value is populated, the value can be used to change the outside caller name or trunk outside name on the telephone system for the call.