Updating CCM line name info from database

knudsen-s · ‎11-17-2007

Hi,

I have a client that has a CCM 4.2, and they have many people that stops and starts in the company. I will like to know if there is anyone that know a good way to update the names on the phones in CCM from a external database.

eg. import line names etc on CCM from a sql database or other extarnal sources.

Soren

gogasca · ‎11-18-2007

Not TAC Supported...!

The bug ID is:

****************************************************************************

*******************************

CSCeg27782 - Bulk update line BAT does not support for 'Line Text Label'

We

already have a enhancement request.

****************************************************************************

*******************************

As a workaround you need to update the field manually or run SQL queries:

To display the current fields use SQL Query Analizer:

select DNorPattern,Label from NumPlan,DeviceNumPlanMAp

where DeviceNumPlanMap.fkNumPlan = NumPlan.Pkid and Label IS NOT NULL

Note: The FkNumPlan will map this record to NumPlan table which contains the

Directory Number in question.

To Update the fields use for example:

****************************************************************************

***********

update devicenumplanmap

set devicenumplanmap.label = ''

from devicenumplanmap

****************************************************************************

***********

This will set the Line Text Label to blank, but it is needed to reset the

CCM service for both/all server in order to changes take place at IP phones.

Note: Resetting the CCM service will cause all your phones to go

unregistered and register back.

You can create your SQL query now that you know the fields accordingly.

What you need to do is create a TXT file then insert it into a new table in

CCMXXX DB what I did was create a Table called BAT (Have a good backup

before doing this)

and a file which contains the following:

"3001","Manager"

"3002","Assistant"

"3003","Employee"

(Quotes are only for being recognized as separated fileds in Export Data

Tool)

Insert it using Import and Export Data Tool under SQL Tools (Which also

creates the Table automatically for you).

Once the table is inserted it contains 2 columns Col001 and Col002

Table BAT

----------------------------------------------------------------------------

----------

Col001 Col002

----------------------------------------------------------------------------

----------

3001 Manager

3002 Assistant

3003 Employee

I run the following query and voila:

----------------------------------------------------------------------------

------

update DeviceNumPlanMap

set Label = (select Col002 from BAT,NumPlan where DNorPattern = Col001 and

DeviceNumPlanMap.fkNumPlan = NumPlan.Pkid);

----------------------------------------------------------------------------

------

Hope this helps.

Note again that this is NOT TAC Supported and is a BAT limitation.