GoldMine Web (GMWEB) - 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 Web (GMWEB) - Server 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 Web
    2. On the CONTACTS page try to open any contact record

    [RESULT] Server 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 10:27:56,638 - [ERROR] Thread id[8] - 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:536d7592-9768-4f9a-a70c-ee8693db3ccf
    Error Number:207,State:1,Class:16
    2015-08-05 10:27:56,639 - [ERROR] Thread id[8] - 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')

    - While the field in question is also mentioned in the GMConnect.Log file the following queries could be also run in order to determine missing user defined fields in either CONTACT2 or CONTUDEF which may apply to this scenario:

    --Columns in CONTUDEF not in CONTACT2

    SELECT * FROM Contudef

    LEFT JOIN syscolumns ON

    (Contudef.field_name = syscolumns.name

    AND id IN (SELECT id FROM sysobjects

    WHEREname = 'contact2'AND type = 'u'))

    WHEREdbfname = 'contact2'

    AND syscolumns.name IS null

    ORDER BY field_name



    --Columns in CONTACT2 not in CONTUDEF

    SELECT * FROM syscolumns

    LEFT JOIN contudef ON

    (syscolumns.name = contudef.field_name

    ANDcontudef.dbfname ='contact2')

    WHERE id IN (SELECT id FROM sysobjects

    WHEREname = 'contact2'AND type = 'u')

    ANDcontudef.recid ISnull AND name <> 'recid'

    ORDER BY name




    - 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
    - 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 needed 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 (For Example GoldSync)

    2.  Make sure that all users are logged off or have closed all applications  with an integration into GoldMine (For Example 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. Delete Button





    9. 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 Web


    [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 (For Example GoldSync)

    2.  Make sure that all users are logged off or have closed all applications  with an integration into GoldMine (For Example 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' and 'Verify the data and synchronization information' > Next

    11. Although there should be no user logged in anymore it might be  recommended to select '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 Web


    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 (For Example GoldSync)
    2. Make sure that all users are logged off or have closed all applications with an integration into GoldMine (For Example 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 Web
     
    [ADDITIONAL NOTE]
    - If there are further errors experienced in GoldMine Web, make sure to review again the GMConnect.log file and also the GMWeb.log  as also other incorrect data may cause the same behavior and error message on screen