GoldMine Web (GMWEB) - Server Error: Error, failed to get activity list. - when opening ACTIVITIES page in GoldMine Web for all users who have any activity scheduled

Version 1

    Details

    GoldMine Web (GMWEB) - Server Error: Error, failed to get activity list. - when opening ACTIVITIES page in GoldMine Web for all users who have any activity scheduled





    The GMConnect.log file shows

    2016-12-09 08:03:27,955 - [ERROR] Thread id[10] - GoldMine.Models.ApplicationExceptions.ClientInformationException: Error, one of the arguments provided to a method is not valid.,
    System.ArgumentException: Object of type 'System.String' cannot be converted to type 'System.Byte[]'.
       at System.RuntimeType.TryChangeType(Object value, Binder binder, CultureInfo culture, Boolean needsSpecialCast)
       at System.RuntimeType.CheckValue(Object value, Binder binder, CultureInfo culture, BindingFlags invokeAttr)
       at System.Reflection.MethodBase.CheckArguments(Object[] parameters, Binder binder, BindingFlags invokeAttr, CultureInfo culture, Signature sig)
       at System.Reflection.RuntimeMethodInfo.InvokeArgumentsCheck(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture)
       at System.Reflection.RuntimeMethodInfo.Invoke(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture)
       at QueryBuilder.Entity`1.SetPopertyValueFromDataReader(PropertyInfo prop, Object obj, SqlDataReader reader, Int32 iCol)
       at QueryBuilder.Entity`1.SetValueFromDataReader(PropertyInfo prop, Object obj, SqlDataReader reader, Int32 iCol, SelectStruct sel)
       at QueryBuilder.Entity`1.CreateResultingEntity(List`1 sel, SqlDataReader reader)
       at QueryBuilder.Entity`1._GetResult(ICustDatabase database, Boolean CheckIfEntityCanBeAdd)
    2016-12-09 08:03:27,955 - [ERROR] Thread id[10] - GoldMine.Models.ApplicationExceptions.ClientInformationException: Error, failed to get activity list.,
    GoldMine.Models.ApplicationExceptions.ClientInformationException: Error, one of the arguments provided to a method is not valid.
       at GoldMine.Models.Log.GMLoggerFactory.GMLogger.ErrorClientAndServer(Object customMessage, Exception e)
       at QueryBuilder.Entity`1._GetResult(ICustDatabase database, Boolean CheckIfEntityCanBeAdd)
       at QueryBuilder.Entity`1.GetResultSecured(ICustDatabase database)
       at GoldMine.Models.ActivityModels.ActivityRepository.GetActivityList(String sFields, Expression`1 calExpression, Expression`1 caldefExpression, String OrderBy, Nullable`1 From, Nullable`1 To)


    Resolution

    [CAUSE]
    - The structure of the CAL table is not correct and needs to be verified
    - This might be due to a not performed database revision or any other back end modification

    [STEPS FOR VERIFICATION AND RESOLUTION]

    1. Via a SQL Query on the GoldMine Database get the structure of the CAL table


    SELECT COLUMN_NAME AS ColumnName, DATA_TYPE AS DataType, CHARACTER_MAXIMUM_LENGTH AS CharacterLength
    FROM INFORMATION_SCHEMA.COLUMNS
    WHERE TABLE_NAME = 'Cal'

    >> This will result in a list with the most important structure information of the CAL table

    ColumnName    DataType    CharacterLength
    USERID        varchar     8
    ACCOUNTNO     varchar     20
    ONDATE        datetime    NULL
    ONTIME        varchar     5
    ENDDATE       datetime    NULL
    ALARMFLAG     varchar     1
    ALARMTIME     varchar     5
    ALARMDATE     datetime    NULL
    ACTVCODE      varchar     3
    RSVP          varchar     1
    DURATION      smallint    NULL
    RECTYPE       varchar     1
    ACONFIRM      varchar     3
    APPTUSER      varchar     10
    STATUS        varchar     4
    DIRCODE       varchar     10
    NUMBER1       float       NULL
    NUMBER2       float       NULL
    COMPANY       varchar     60
    REF           varchar     80
    NOTES         text        2147483647
    LINKRECID     varchar     15
    LDOCRECID     varchar     15
    LOPRECID      varchar     15
    CREATEBY      varchar     8
    CREATEON      datetime    NULL
    CREATEAT      varchar     5
    LASTUSER      varchar     8
    LASTDATE      datetime    NULL
    LASTTIME      varchar     5
    recid         varchar     15
    EXT           varchar     5
    SERVICE       text        2147483647
    CALDEF_ID     varchar     15
    ORIG_DATE     datetime    NULL
    ORIG_TIME     varchar     5
    IS_EXCEPTION  smallint    NULL
    CALDEFEX_ID   varchar     15
    FLAGS         int         NULL
    ATTENDEES_EX  varchar     100
    GLOBALID      varchar     200

    2. Compare with the following current GoldMine 2016.1 structure

    ColumnName    DataType    CharacterLength
    USERID        varchar     8
    ACCOUNTNO     varchar     20
    ONDATE        datetime    NULL
    ONTIME        varchar     5
    ENDDATE       datetime    NULL
    ALARMFLAG     varchar     1
    ALARMTIME     varchar     5
    ALARMDATE     datetime    NULL
    ACTVCODE      varchar     3
    RSVP          varchar     1
    DURATION      smallint    NULL
    RECTYPE       varchar     1
    ACONFIRM      varchar     3
    APPTUSER      varchar     10
    STATUS        varchar     4
    DIRCODE       varchar     10
    NUMBER1       float       NULL
    NUMBER2       float       NULL
    COMPANY       varchar     60
    REF           varchar     80
    NOTES         image       2147483647
    LINKRECID     varchar     15
    LDOCRECID     varchar     15
    LOPRECID      varchar     15
    CREATEBY      varchar     8
    CREATEON      datetime    NULL
    CREATEAT      varchar     5
    LASTUSER      varchar     8
    LASTDATE      datetime    NULL
    LASTTIME      varchar     5
    EXT           varchar     5
    SERVICE       text        2147483647
    CALDEF_ID     varchar     15
    ORIG_DATE     datetime    NULL
    ORIG_TIME     varchar     5
    IS_EXCEPTION  smallint    NULL
    CALDEFEX_ID   varchar     15
    FLAGS         smallint    NULL
    ATTENDEES_EX  varchar     100
    GLOBALID      varchar     200
    recid         varchar     15


    3. Correct any cases where the structure of the CAL is different from the required structure.

    - Addressing any wrong structure may require different steps and may need to involve a partner.
    - The different NOTES field structure seems to be caused by 'restore' of a  CAL table with manually adjusting the new fields but not addressing the  NOTES fields structure change.
    - A REBUILD of the individual file may address the structure but will not for the above scenario

    1. Make sure to have a full running backup 

    2. Make sure that all users are logged out of GoldMine (it is recommended that this process is done outside of business hours)
    3. In SQL Server Management Studio to re-set the ENVIRONMENT  >> DB_VERSION row field VAL is blank (not necessary in this case but if the database revision was not performed, also the CONTACT SET tables may require a revision again - set the CONTSUPP->CS_VERSION row field CONTACT is blank)
    4.  Start GoldMine with master rights and confirm the upcoming conversion  dialog so that the database revision is started again (due to the amount  of Mailbox records this may take a longer while)
    5. Start GoldMine and verify some records (especially in Calendar)
    6. Verify the ACTIVITIES page again in GoldMine Web