GoldMine Mobile (GMME) - Server Error - Error, failed to get contact list - the related log file shows ....System.Data.SqlClient.SqlException (0x80131904): Invalid column name 'Userdefined field name'....

Version 1

    Details

    GoldMine Mobile (GMME) - Error - Error, failed to get contact list - the related log file shows ....System.Data.SqlClient.SqlException (0x80131904): Invalid column name 'Userdefined field name'....

    [STEPS TO REPRODUCE]
    1. Log into GoldMine Mobile
    2. Tap Contacts
    3. All Contacts
    4. Try to select / open any contact record
    >> RESULT: Error - Error, failed to get contact list



    3. Reviewing the related GMConnect.Log (by default in the GoldMine Connect installation folder\Logs folder) shows the error

    2015-08-05 16:16:43,961 - [ERROR] Thread id[14] - GoldMine.Models.ApplicationExceptions.ClientInformationException: Error, Run SQLcollation statements error.,
    System.Data.SqlClient.SqlException (0x80131904): Invalid column name 'UTEST'.
       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.TryConsumeMetaData()
       at System.Data.SqlClient.SqlDataReader.get_MetaData()
       at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
       at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite, SqlDataReader ds)
       at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean asyncWrite)
       at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
       at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method)
       at System.Data.SqlClient.SqlCommand.ExecuteReader()
       at QueryBuilder.Entity`1._GetResult(ICustDatabase database, Boolean CheckIfEntityCanBeAdd)
    ClientConnectionId:2e5d91ef-5715-4d82-b3ad-62132f62f6bd
    Error Number:207,State:1,Class:16
    2015-08-05 16:16:44,175 - [ERROR] Thread id[14] - GoldMine.Models.ApplicationExceptions.ClientInformationException: Error, failed to get contact list.,
    GoldMine.Models.ApplicationExceptions.ClientInformationException: Error, Run SQLcollation statements error.
       at GoldMine.Models.Log.GMLoggerFactory.GMLogger.ErrorClientAndServer(Object customMessage, Exception e)
       at QueryBuilder.Entity`1._GetResult(ICustDatabase database, Boolean CheckIfEntityCanBeAdd)
       at GoldMine.Models.ContactModels.ContactRepository.GetContactList(String sFields, Expression`1 expression, String OrderBy, Nullable`1 From, Nullable`1 To)



    Resolution

    [CAUSE]
    -  This means that the UTEST field is defined in the CONTUDEF (available  via Tools >> Configure >> Custom Fields)


    - But it is not added  as a column in the CONTACT2 table - either review structure of CONTACT2  in the SQL Server Management Studio or via SQL Query (review Name  column)



    select * from syscolumns where id = (select id from sysobjects where name ='CONTACT2')

    >> The reason can be either that when setting  up the custom field no necessary rebuild was done, or the rebuild  failed and was not addressed

    [DISCUSSION]

    - Currently the user  defined field is not available within the database and therefore cannot  be used by users, but prior performing any steps, it needs to be  determined by the GoldMine Administrator if the field is really not to  be used or needs to be available within GoldMine.

    [STEPS TO REMOVE THE USER DEFINED FIELD FROM THE CONTUDEF]

    - The following steps are necessary when the user defined field is not necessary within GoldMine

    - For the above example we are using UTEST as a field which is not  necessary within GoldMine and will be removed from the field definition  table (CONTUDEF)

    1. Make sure that all users are logged out of GoldMine and all services related to GoldMine are shut down (e.g. GoldSync)

    2.  Make sure that all users are logged off or have closed all applications  with an integration into GoldMine (e.g. GMWEB, GMME, Outlook, Word etc)

    3. Stop IIS

    4. Make sure to have a full running backup of your GoldMine database

    5. In GoldMine with master rights

    6. Tools >> Configure >> Custom Fields (this opens the User Defined Fields list)

    7.  Browse to and highlight the specific field (for example click on the  Name column header to sort after the User defined field names in the  back end)

    8. Button Delete




    9.  Button Rebuild >>OK
    10. Make sure to restart IIS prior to any other GoldMine related services like GoldSync etc.

    11. Re-test the behavior in GoldMine Mobile


    [STEPS TO MAKE THE USER DEFINED FIELD AVAILABLE]
    - The following steps are necessary when the user defined field needs to  be available within GoldMine for capturing data on a contact record and therefore added via a rebuild to the  CONTACT2 table as further column
    1. Make sure that all users are logged out of GoldMine and all services related to GoldMine are shut down (e.g. GoldSync)

    2.  Make sure that all users are logged off or have closed all applications  with an integration into GoldMine (e.g. GMWEB, GMME, Outlook, Word etc)

    3. Stop IIS

    4. Make sure to have a full running backup of your GoldMine database

    5. In GoldMine with master rights

    6.Tools >> Databases >> Maintain Database…

    7. Individual Files >> Next

    8. Select CONTUDEF and CONTACT2 table from the list >> Next

    9.  Select Rebuild and Pack the database files >> there appears a  message that SQL tables need not to be rebuilt unless a table  restructure is required…  >> confirm this message

    10. Select Sort the database files + Verify the data and synchronization information >> Next

    11. Although there should be no user logged in anymore it might be  recommended to select the Force all users to exit GoldMine with 1 minute  >> Next

    12. Finish

    13. Make sure to restart IIS prior any other GoldMine related services like GoldSync etc.

    14. Re-test the behavior in GoldMine Mobile


    Alternatively  - especially when the GoldMine Administrator wants to verify the data  type and length of the user defined field - also the following steps are  possible.

    1. Make sure that all users are logged out of GoldMine and all services related to GoldMine are shut down (e.g. GoldSync)
    2.  Make sure that all users are logged off or have closed all applications  with an integration into GoldMine (e.g. GMWEB, GMME, Outlook, Word etc)
    3. Stop IIS
    4. Make sure to have a full running backup of your GoldMine database
    5. In GoldMine with master rights
    6. Tools >> Configure >> Custom Fields (this opens the User Defined Fields list)
    7.  Browse to and highlight the specific field (for example click on the  Name column header to sort after the User defined field names in the  back end)
    8. Verify the data type and length, if applicable adjust  via the Properties button (this is usually not recommended and supported  on existing fields and the action is only eligible in assuming that the  CONTACT2 does not have a column for the user defined field as the log  file indicates)
    9. Once reviewed and the user defined field is setup correctly >> Rebuild button >> OK
    10. Make sure to restart IIS prior any other GoldMine related services like GoldSync etc.
    11. Re-test the behavior in GoldMine Mobile
     
    Additional Note:
    If  there are further errors experienced in GoldMine Mobile, make sure to  review again the GMConnect.log file and also GMMobile.log as also other incorrect data may  cause the same behavior and error message on screen