GoldMine Messaging Service does not send out notifications - the GMMessagingService.log shows 'Conversion failed when converting the varchar value '8:' to data type int.'

Version 1

    Details

    GoldMine Messaging Service does not send out notifications - the GMMessagingService.log shows 'Conversion failed when converting the varchar value '8:' to data type int.'

    - The GoldMine Messaging feature provides the possibility to send Alarms for upcoming activities to a mobile device as SMS messages (Short Message Service) or as an Email. This enables users to receive reminders even when they are not logged in to GoldMine Mobile or GoldMine Web. After being configured in GoldMine Premium Edition, users can receive text message reminders, and audible or vibration alerts.

    - For further information about this feature and the necessary configuration, please refer to the Online help via Help > GoldMine Help and then the chapters:

    a. Administration Guide > Configuring GoldMine Connect > Configure Alarm Messages for GoldMine Connect
    b. Administration Guide > User Administration > Managing User Options > Setting Options for the Messaging Tab
    c. Administration Guide > User Administration > Configuring System Settings > Mobile Device Messaging

    - Once the setup and configuration is completed the GoldMine Messaging Service should be started in the Windows Services.
    - In case any unexpected behavior is experienced with the GoldMine Messaging Service the first information can be found in the
    a. GMMessagingService.log file which is located in the GoldMine Connect installationfolder\Logs\MessagingService folder for example in C:\Program Files (x86)\GoldMine Connect\Logs\MessagingService
    b. The Windows Event viewer log file

    - This current article provides information how to identify and resolve any issues, when the GMMessagingService.log shows the red marked error

    2015-05-28  23:16:47,144 - [INFO] Thread id[5] - Enter the Send SMS model2015-05-28  23:16:47,182 - [ERROR] Thread id[5] -  System.Data.SqlClient.SqlException (0x80131904): Conversion failed when converting the varchar value '8:' 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:f0140bd7-0c27-4821-a03b-6f86373eadaaError  Number:245,State:1,Class:162015-05-28 23:17:46,827 - [INFO] Thread  id[5] - Enter the Send SMS model2015-05-28 23:17:46,827 - [ERROR] Thread  id[5] - System.Data.SqlClient.SqlException (0x80131904): Conversion failed when converting the varchar value '8:' 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:f0140bd7-0c27-4821-a03b-6f86373eadaaError  Number:245,State:1,Class:16


    Resolution

    [CAUSE]
    - There are CAL  records for one or several of the notified users, which have invalid values in the ALARMTIME field, for example set to values like 123, 1245pm, -1-4 . instead of hh:mm format
    - Once the messaging service evaluates such a record it will break,  because it cannot identify a proper time formatting.

    sample screenshots






    - Those occurrences where the ALARMTIME field does not have a valid time format (hh:mm) need to be corrected


    [STEPS]
    1. A query which should help to identify CAL records with invalid ALARMTIME values is for example

    select ACCOUNTNO, RECTYPE, ONDATE, ONTIME, ALARMTIME, ALARMDATE from CAL where USERID in  ('USERA', 'USERB','USERC','MASTER') and alarmdate <= '2015-05-29  22:11:48.773' order by ALARMTIME asc

    - The query needs to be adjusted to reflect the set USERNAMES who shall get notifications, if this is unknown it should be considered to verify the alarmed  activities for all users.

    select ACCOUNTNO, RECTYPE, ONDATE, ONTIME, ALARMTIME, ALARMDATE from CAL where alarmdate <= '2015-05-29  22:11:48.773' order by ALARMTIME asc

    - The query needs to be adjusted to reflect the desired alarmdate value, for example a few days in the future. While it is recommended to check the whole CAL table this may save time but should then be documented in case the error reappears. A query for the whole CAL table would be for example 

    select ACCOUNTNO, RECTYPE, ONDATE, ONTIME, ALARMTIME, ALARMDATE from CAL order by ALARMTIME asc

    2. In the upcoming list all occurrences where the ALARMTIME field is not in the appropriate format hh:mm for the specific activity type (see RECTYPE column) need to be corrected for example via

    3. Identify the contact record via the Accountno
    4. Browse in GoldMine Premium Edition for example via a search to the identified contact record
    5. Browse to the contact record's Pending tab
    6. Identify via the RECTYPE and / or ONDATE and/or ONTIME information from the above list the specific pending item
    7. Right Click > Edit
    8. Set a proper alarm or simply save the activity without any change as this will remove the wrong alarm also and therefor correct the ALARMTIME value
    9. Repeat for every CAL record which appears incorrect.
    10. Once all wrong occurrences are corrected > in Windows Services > Restart the GoldMine Messaging Services
    11. Verify the GMMessagingService.log file which shows now as expected no conversion error anymore
     
    2015-05-29 00:40:59,334 - [INFO] Thread id[5] - Enter the Send SMS model
    2015-05-29 00:41:58,747 - [INFO] Thread id[5] - Enter the Send SMS model
    2015-05-29 00:42:58,768 - [INFO] Thread id[7] - Enter the Send SMS model
    2015-05-29 00:43:58,782 - [INFO] Thread id[7] - Enter the Send SMS model
    2015-05-29 00:44:58,797 - [INFO] Thread id[5] - Enter the Send SMS model
    2015-05-29 00:45:58,812 - [INFO] Thread id[7] - Enter the Send SMS model
    2015-05-29 00:46:58,826 - [INFO] Thread id[7] - Enter the Send SMS model
    2015-05-29 00:47:58,842 - [INFO] Thread id[5] - Enter the Send SMS model
    2015-05-29 00:48:58,855 - [INFO] Thread id[7] - Enter the Send SMS model
    2015-05-29 00:49:58,856 - [INFO] Thread id[7] - Enter the Send SMS model
    2015-05-29 00:50:58,869 - [INFO] Thread id[7] - Enter the Send SMS model
    2015-05-29 00:51:58,893 - [INFO] Thread id[7] - Enter the Send SMS model
    2015-05-29 00:52:58,906 - [INFO] Thread id[5] - Enter the Send SMS model
    2015-05-29 00:53:58,919 - [INFO] Thread id[4] - Enter the Send SMS model
    2015-05-29 00:54:58,933 - [INFO] Thread id[7] - Enter the Send SMS model
    2015-05-29 00:55:58,946 - [INFO] Thread id[7] - Enter the Send SMS model
    2015-05-29 00:56:58,959 - [INFO] Thread id[5] - Enter the Send SMS model
    2015-05-29 00:57:58,972 - [INFO] Thread id[7] - Enter the Send SMS model
    2015-05-29 00:58:58,986 - [INFO] Thread id[7] - Enter the Send SMS model

    Instead of previous error messages

    2015-05-28 23:16:47,144 - [INFO] Thread id[5] - Enter the Send SMS model
    2015-05-28  23:16:47,182 - [ERROR] Thread id[5] -  System.Data.SqlClient.SqlException (0x80131904): Conversion failed when  converting the varchar value '8:' 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:f0140bd7-0c27-4821-a03b-6f86373eadaa
    Error Number:245,State:1,Class:16
    2015-05-28 23:17:46,827 - [INFO] Thread id[5] - Enter the Send SMS model
    2015-05-28  23:17:46,827 - [ERROR] Thread id[5] -  System.Data.SqlClient.SqlException (0x80131904): Conversion failed when  converting the varchar value '8:' 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:f0140bd7-0c27-4821-a03b-6f86373eadaa
    Error Number:245,State:1,Class:16