Invalid entry in HDLOCK_HDW causes issues with Autoemail.

Version 6

    Problem:

    Autoemail creates new incidents but does not create the assignment to the Initial Analyst or Group as setup on the New Incident tab in Autoemail configuration.

    Other issues with Automail may also be experienced.

     

    The Hderror.log will show the following errors:

     

    ********************************** 01/04/2011 14:47:26 *********************************
    About to Log on as Service
    **************************************************************************************
    ********************************** 01/04/2011 14:47:26 *********************************
    Mail Server = TRAINING-DC
    **************************************************************************************
    ********************************** 01/04/2011 14:47:26 *********************************
    Mail Box = Administrator
    **************************************************************************************
    ********************************** 01/04/2011 14:47:26 *********************************
    Logged on to mail
    **************************************************************************************
    ********************************** 01/04/2011 14:47:26 *********************************
    SUCCESS mailparameters were validated
    **************************************************************************************
    ********************************** 01/04/2011 14:47:27 *********************************
    SUCCESS Busobjinterface validated
    **************************************************************************************
    ********************************** 01/04/2011 14:47:31 *********************************
    [Microsoft][ODBC SQL Server Driver][SQL Server]Cannot insert duplicate key row in object 'dbo.QUEUE_HDW' with unique index 'QUEUE_HDW0'.BuildObject clsNewCall
    **************************************************************************************
    ********************************** 01/04/2011 14:47:31 *********************************
    SUCCESS BuildObject
    **************************************************************************************
    ********************************** 01/04/2011 14:47:31 *********************************
    Exiting Inbound.exe
    **************************************************************************************

     

    Running an SQL profile will show the following SQLstatements repeated many times (if monioting SQL:StmtStarting and SQL BatchStarting)

     

    INSERT INTO HDLOCK_HDW (HDESKID_HDW,TSOPER_HDW,TSGRP_HDW) VALUES ('AUT', 'AUTOEMAIL', 'AUTOEMAIL' )
    select * from INSERT INTO HDLOCK_HDW (HDESKID_HDW,TSOPER_HDW,TSGRP_HDW) VALUES ('AUT', 'AUTOEMAIL', 'AUTOEMAIL' )
    

     

    The inbound.exe will exit and the last 2 lines in the profile will look like this:

     

    INSERT INTO QUEUE_HDW ( QUEUEINSTANCE_HDW,TABLEID_HDW,OPERATION_HDW,SERIAL_HDW,BUFFER_HDW,APPID_HDW,TSDATE_HDW,TSTIME_HDW,ERRORFLAG_HDW) VALUES ('CALLID=00000000000000108593 ','CALL','I',0,'110401294704','DESIGNERPLUS',131793921,237968640,0)
    select * from INSERT INTO QUEUE_HDW ( QUEUEINSTANCE_HDW,TABLEID_HDW,OPERATION_HDW,SERIAL_HDW,BUFFER_HDW,APPID_HDW,TSDATE_HDW,TSTIME_HDW,ERRORFLAG_HDW) VALUES ('CALLID=00000000000000108593 ','CALL','I',0,'110401294704','DESIGNERPLUS',131793921,237968640,0)
    

     

    Cause:

    This is caused by an invalid lock entry existing in the HDLOCK_HDW table.  The Lock entry had a HDESKID_HDW=AUT, TSDATE_HDW and TSTIME_HDW=NULL, TSOPER_HDW and TSGRP_HDW=AUTOEMAIL.

     

    The Invalid Lock causes Autoemail to form invalid SQL statements which prevents the assignment from being created.

     

    Screentshot of Invalid lock:

    InvalidLock.JPG

     

    Solution:

    Make sure everyone is logged out of Helpdesk/SupportCenter, stop any Touchpaper Services which might be running such as Autoemail, Monitor, Infomine etc... and then run the attached script against the Helpdesk database