Cisco Unity – Enabling Alternate Greeting
During the day I work for an Association Management company: a company that manages and provides services to groups, organizations, and associations who lack or do not want to manage the infrastructure of their organization. We provide the client staff to support membership, meeting planning, and administrative aspects of each individual organization. We also provide the support staff which handles web design and development, human resources, creative and graphic design, database services, accounting service, and last but not least the technology infrastructure.
For the past few years we’ve relied on our HR department to record and enable voicemail greetings indicating whether the office is closed due to inclimate weather, holidays, or odd days off. Regardless of the situation the process has always been trial and error: HR recorded the greeting in the wrong section, other times HR forgot to enable the greeting, and sometimes the greetings weren’t enabled automatically.
In most cases a call handler would have got the job done but HR did not like how strung together it sounded. Also, there was misinformation in their Technology guide instructing them to record the the “alternate” greeting instead of the “closed” greeting for days off.
After messing with Query Analyzer and Enterprise Manager I found that the TimeExpires field in the MessagingRule table was set or reset by Unity to enable or disable a specific greeting. I crafted two SQL queries to test my theory on TimeExpires:
Enable alternate (set)
UPDATE MessagingRule SET TimeExpires = NULL WHERE ParentAlias = 'ch_UpbeatUser' and Alias = 'Alternate'
and
Enable standard (reset)
UPDATE MessagingRule SET TimeExpires = DATEADD(hh, -24, GETDATE()) WHERE ParentAlias = 'ch_UpbeatUser' and Alias = 'Alternate'
Both worked! Taking a look at the ParentAlias values for all our clients I modified the original UPDATE statements rolled them into stored procedures:
CREATE PROCEDURE SetAlternateGreeting
AS
BEGIN
IF EXISTS( SELECT HolidayDate FROM Holiday WHERE
CAST(FLOOR(CAST( getdate() AS float)) AS smalldatetime) =
CAST(FLOOR(CAST( HolidayDate AS float)) AS smalldatetime) )
BEGIN
UPDATE MessagingRule SET TimeExpires = NULL
WHERE
ParentAlias IN ('ch_thisClient', 'ch_thatClient', 'ch_likethisClient', 'ch_likethatClient', 'ch_chillTo', 'ch_TheNext', 'ch_Client')
AND
Alias = 'Alternate'
END
END
and
CREATE PROCEDURE DisableAlternateGreeting
AS
BEGIN
IF EXISTS(
SELECT HolidayDate FROM Holiday WHERE
(DATEADD(hh, -24, CAST(FLOOR(CAST( getdate() AS float)) AS smalldatetime)) =
CAST(FLOOR(CAST( HolidayDate AS float)) AS smalldatetime) ) )
BEGIN
UPDATE MessagingRule SET TimeExpires = (CAST(DATEADD(hh, -24, GETDATE()) as smalldatetime)) WHERE
ParentAlias IN ('ch_thisClient', 'ch_thatClient', 'ch_likethisClient', 'ch_likethatClient', 'ch_chillTo', 'ch_TheNext', 'ch_Client')
AND
Alias = 'Alternate'
END
END
Next I launched Task Scheduler and setup a job to run nightly at 01:00:00 am. A test that evening revealed that I was missing the appropriate permissions. The SQL Agent was failing with the following errors:
1. The job failed. The Job was invoked by Schedule 6 (Alternate Greetings Exec Recurring). The last step to run was step 1 (Set the Alternate Greeting If Date Matches).
2. Executed as user: NT AUTHORITY\SYSTEM. Could not find stored procedure ‘UnityDb.SetAlternateGreeting’. [SQLSTATE 42000] (Error 2812). The step failed.
The changes I made were as follows:
1. Modified the permissions on the Scheduled task to run as MYDOMAIN\UnityInstall
2. Corrected the stored procedure syntax: EXEC UnityDb..SetAlternateGreeting Apparently, if you’re not naming the database user in the database object you have to put two dots in there.
I tested the revised job and it ran without errors. Your version of Unity might differ a bit, but you can see how easy it would be to create a greetings management system using stored procedures. Depending on your time and budget I would let Cisco and Unity do the grunt work unless there’s some functionality that’s absolutely lacking. It’s great that you can add functionality to Unity with a few SQL queries and a bit of investigation!
You can download the code in the Projects section under a Attribution Non-Commercial license.
Tags: cisco
Leave a Reply
You must be logged in to post a comment.