Quotewerks does create alarm times in h:mm format within GoldMine database, causing the GoldMine Messaging Service not to send out notifications and having errors in the log file Conversion failed when converting the varchar value 'x:' to data type int.'

Version 1

    Details

    Quotewerks does create alarm times in h:mm format within GoldMine database, causing the GoldMine Messaging Service not to send out notifications and having errors in the log file Conversion failed when converting the varchar value 'x:' to data type int.'

    Important information:
    Below information is provided by GoldMine Technical Support only as-is and without any warranty of any kind as Quotewerks is not a HEAT Software / GoldMine developed and/or maintained application. HEAT Software / GoldMine Technical Support does not provide any support for Quotewerks. Any screenshots for this Knowledge Article taken from Quotewerks and any possible non GoldMine or non Heat Software links are only provided as-is and content, functionality and availability may change at any time without any notice

    [PRE-REQUISITS]
    - test this behavior at any time with a possible 1 digit hour like 8:00 am (as GoldMine Messaging Service will send reminders for upcoming Alarms) and the behavior is not replicable for 2 digits hours (in the below example the current time as set to 3:30 am)
    - having Quotewerks installed and configured to integrate with GoldMine
    - having GoldMine Messaging Service installed
    - having a user (in below example MASTER) configured to retrieve reminders via the GoldMine Messaging Service
    1. Tools >> Options
    2. Tab Messaging
    3. Activate Send activity alarms as emails or text messages + provide a proper address where the reminder should be sent to
    4. OK


    [STEPS TO PREPARE] - Verification that the GoldMine Messaging Service does send reminders properly
    - in Windows Services
    1. Stop the GoldMine Messaging Service. (this is only for demonstration)

    - in GoldMine demo database
    1. Browse to Bea Defkow
    2. Schedule >> Appointment
    a. enter any desired data
    b. Date: select today's date from the calendar  
    c. Time: select 3:50 am from the clock
    d. Duration: 30 minutes
    e. activate Alarm check box and set it to 3:38 am
    3. Schedule
    4. Close GoldMine for now

    - in Windows Services
    1. Start the GoldMine Messaging Service. (this is only for demonstration)

    - Verification that the reminder was sent
    1. either via checking your phone/email
    >> RESULT: a reminder is retrieved via email


    2. or in the GMMessagingService.Log file in the GoldMine Connect installation folder\Logs\MessagingService
    >> RESULT:  The log file shows that an email was queued with he expected details.
    2015-08-20 03:40:50,047 - [DEBUG] Thread id[5] - Command:<GMAPI call="QueueMail" SessionID="1">
      <data name="From"></data>
      <data name="To">michaela.witxxx</data>
      <data name="Cc"></data>
      <data name="Bcc"></data>
      <data name="Subject">GoldMine Alarm</data>
      <data name="BodyText">Date: 8/20/2015
    Time: 3:50 AM
    Link: http://w12r2/GoldMine/?call_id=IKW8WPV%28%3A%2F%26%26%20W%3C

    Reference: Demo walk through
    Company: Cadten Engineering Ltd.
    Contact: Bea Defkow
    Phone: (708)555-3311</data>
      <data name="User">MASTER</data>
      <data name="AccountNo">B4100759594%8`)\ Mic</data>
      <data name="UserProfile">MASTER</data>
      <data name="LinkedContact"></data>
      <data name="OpptyRecID"></data>
      <data name="ProjectRecID"></data>
      <data name="CaseRecID"></data>
      <data name="SendNow">1</data>
      <data name="StoreInDB">0</data>
    </GMAPI>

    2015-08-20 03:41:47,468 - [INFO] Thread id[7] - Enter the Send SMS model


    - in Windows Services
    1. Stop the GoldMine Messaging Service. (this is only for demonstration)


    [STEPS TO REPRODUCE]
    1. Start GoldMine
    2. Browse to Bea Defkow
    - in Quotewerks (steps may vary in versions and workflows)
    3. Contact Button >> into the Quotewerks form
    4. Save Button (this will save a Quote)


    5. In the Save Quote dialog >> Select Create or Update a Follow up Call
    6. OK
    7. In the Schedule GoldMine follow up call dialog
    8. Set the time to 4:03 am
    9. Activate the Alarm check box

    10. OK

    - in Windows Services
    1. Start the GoldMine Messaging Service again (this is only for demonstration)

    - Verification that the reminder was sent
    1. either via checking your phone/email
    >> RESULT: you are not getting any notification on the phone or via email
    2. or in the GMMessagingService.Log file in the GoldMine Connect installation folder\Logs\MessagingService
    >> RESULT: The GMMessagingService.Log shows the following error:

    Thread id[5] - Enter the Send SMS model
    2015-08-20 03:50:34,329 - [ERROR] Thread id[5] - System.Data.SqlClient.SqlException (0x80131904): Conversion failed when converting the varchar value '4:' to data type int.
       at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
       at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
       at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
       at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
       at System.Data.SqlClient.SqlDataReader.TryHasMoreRows(Boolean& moreRows)
       at System.Data.SqlClient.SqlDataReader.TryReadInternal(Boolean setTimeout, Boolean& more)
       at System.Data.SqlClient.SqlDataReader.Read()
       at System.Data.Linq.SqlClient.ObjectReaderCompiler.ObjectReaderBase`1.Read()
       at System.Data.Linq.SqlClient.ObjectReaderCompiler.ObjectReader`2.MoveNext()
       at System.Linq.Buffer`1..ctor(IEnumerable`1 source)
       at System.Linq.Enumerable.ToArray[TSource](IEnumerable`1 source)
       at DBHelper.GMDataClassesHelper.getCalforSMS()
    ClientConnectionId:0997576f-64b9-4d82-b5ba-c353584acc10

    The reason for this is that Quotewerks entered the ALARMTIME in the GoldMine database in the format 4:03 instead the expected format 04:03







    Resolution

    [CAUSE]
    - Alarms created by Quotewerks and set only to  single digit hours like 4:03 or even set with a leading '0' like 04:03  the ALARMTIME value will end up in the GoldMine database (CAL table)  also in the h:mm format and the leading '0' will be stripped out, this  means the ALARMTIME value is in an incorrect format which cannot be interpreted by the GoldMine Messaging Service
    - This is a 'undesired' behavior how Quotewerks writes the data into the GoldMine database and is currently not considered to be an GoldMine related behavior/issue

    [RESOLUTION]
    - Existing wrong formatted records need to be identified and corrected - see related Knowledge Article # 17612 - GoldMine Messaging Service does not send out notifications - the GMMessagingService.log shows 'Conversion failed when converting the varchar value '8:' to data type int.'
    - in order to prevent this behavior to be caused by Quotewerks created alarms, it is strongly to train Quotewerks users to always set a 2 digit hours like 10:xx, 11:xx etc. in Quotewerks when scheduling follow up calls with alarms as even when using a leading '0' as in 04:50 am the '0' will be stripped out

    [WORKAROUND]
    1. Start GoldMine
    2. Browse to Bea Defkow
    - in Quotewerks (steps may vary in versions and workflows)
    3. Contact Button >> into the Quotewerks form
    4. Save Button (this will save a Quote)
    5. In the Save Quote dialog >> Select Create or Update a Follow up Call
    6. OK
    7. In the Schedule GoldMine follow up call dialog
    8. Set the time to 10:00 am
    9. Activate the Alarm check box

    10. OK

    - in Windows Services
    1. Start the GoldMine Messaging Service again (this is only for demonstration)

    - Verification that the reminder was sent
    1. either via checking your phone/email


    2. or in the GMMessagingService.Log file in the GoldMine Connect installation folder\Logs\MessagingService
    >> RESULT: The GMMessagingService.Log shows the following information which indicates that the action was sucessful: 

     
    2015-08-20 10:00:32,245 - [DEBUG] Thread id[5] - Command:<GMAPI call="QueueMail" SessionID="1">
      <data name="From"></data>
      <data name="To">michaela.wittxxxx</data>
      <data name="Cc"></data>
      <data name="Bcc"></data>
      <data name="Subject">GoldMine Alarm</data>
      <data name="BodyText">Date: 8/20/2015
    Time: 10:00 AM
    Link: http://w12r2/GoldMine/?call_id=IKWEW20%25R4L%60%20W%3C

    Reference: Document #AAAQ1003   - Cadten Engineering Ltd. - Thursday, August 20, 2015
    Company: Cadten Engineering Ltd.
    Contact: Bea Defkow
    Phone: (708)555-3311</data>
      <data name="User">MASTER</data>
      <data name="AccountNo">B4100759594%8`)\ Mic</data>
      <data name="UserProfile">MASTER</data>
      <data name="LinkedContact"></data>
      <data name="OpptyRecID"></data>
      <data name="ProjectRecID"></data>
      <data name="CaseRecID"></data>
      <data name="SendNow">1</data>
      <data name="StoreInDB">0</data>
    </GMAPI>

    Additional Notes:
    - Same as GoldMine Quotewerks relies on the current used regional settings. Quotewerks provides the possibility to check such settings (Read-Only) via Tools >> Options >> Tab Regional
    - even when setting both the Time formats (long and short format) in Microsoft Windows to hh:mm:ss tt / hh:mm the behavior did not change
    - Due to the same reason Quotewerks alarms which are created with the wrong format will also not show in a pop-up alarm in GoldMine when using GoldMine Premium Edition GMPE. (see also related Knowledge Article # 18567 - Quotewerks does create alarm times in h:mm format within GoldMine database, causing the Pop-Up alarm window not to appear for such alarmed activities)