Failed to to copy records from CONTACT2 to GOLDMINECOPYTEMPTABLE when performing a REBUILD on the CONTACT2 table.

Version 1

    Details

    Failed to to copy records from CONTACT2 to GOLDMINECOPYTEMPTABLE when performing a REBUILD on CONTACT2 table

    [INFORMATION]
    - Since GoldMine Premium Edition it is usually not necessary anymore to Rebuild the GoldMine database from within GoldMine, when activating the Rebuild and Pack the database files in the Database Maintenance wizard there appears even the following warning



    Nevertheless a Rebuild is occasionally necessary to be performed on the (user defined) fields related tables, for example when

    -
    Creating new user defined fields via Tools > Configure > Custom Fields or via Right Click > New Field on a the Fields tab or in Screen Designer, also especially when experiencing any unexpected behaviors or error messages for example
    - When drilling done into a Contact record on GoldMine Mobile
    - When the database fields list do not appear complete within the GoldMine Plus for Microsoft Word or Microsoft Excel integration
    it is necessary to make sure to realign the CONTUDEF and the CONTACT2 table

    [STEPS TO REPRODUCE]

    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 GMME, Outlook, Word etc)

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

    4. In GoldMine with master rights

    5. Tools > Databases > Maintain Database…
    6. Individual Files > Next

    7. Select FIELDS5, CONTUDEF, CONTACT2 tables from the list > Next

    8. 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

    9. Select 'Sort the database files' and 'Verify the data and synchronization information' > Next

    10. 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

    11. Finish


    [RESULT]
    An error message appears: Failed to to copy records from CONTACT2 to GOLDMINECOPYTEMPTABLE



     


    Resolution

    [CAUSE]
    - There is a mismatch between the field definitions (CONTUDEF) and the CONTACT2 table - such mismatches can be
    a. Certain columns exist in CONTACT2 table but are not defined in Tools > Configure > Custom Fields (CONTUDEF)

    b. Data types of columns in the CONTACT2 do not match the definition for the column in the Tools > Configure > Custom Fields (CONTUDEF) for example UBDAY is defined in CONTACT2 as a varchar(10) field while it is defined in CONTUDEF as a date date type

    An indicator for this mismatch between CONTACT2 and CONTUDEF is the following Event viewer log entry 

    GM version: 2016.1.0.188 R Thread ID 5560:

    ADO Error in AdoCommand::execute INSERT INTO [dbo].[GOLDMINETEMPCOPYTABLE] ( [ACCOUNTNO] , [CALLBACKON] , [CALLBACKAT] , [CALLBKFREQ] , [LASTCONTON] , [LASTCONTAT] , [LASTATMPON] , [LASTATMPAT] , [MEETDATEON] , [MEETTIMEAT] , [COMMENTS] , [PREVRESULT] , [NEXTACTION] , [ACTIONON] , [CLOSEDATE] , [USERDEF01] , [USERDEF02] , [USERDEF03] , [USERDEF04] , [USERDEF05] , [USERDEF06] , [USERDEF07] , [USERDEF08] , [USERDEF09] , [USERDEF10] , [USERDEF11] , [USERDEF12] , [USERDEF13] , [USERDEF14] , [USERDEF15] , [USERDEF16] , [ULINKEDIN] , [UFACEBOOK] , [UTWITTER] , [UREP] , [UNAICS] , [UBRDETAIL] , [UCREATEON] , [UCDATE] , [USTATUS] , [UCREATEAT] , [UACCNO] , [ULASTUSER] , [ULASTDATE] , [URECNO] , [ULASTTIME] , [URECIDNO] , [UTEST] , [UHMAIL] , [UHMAILID] , [U] , [UROLAND] , [UTEST2] , [RECID] )
     SELECT [ACCOUNTNO] , [CALLBACKON] , [CALLBACKAT] , [CALLBKFREQ] , [LASTCONTON] , [LASTCONTAT] , [LASTATMPON] , [LASTATMPAT] , [MEETDATEON] , [MEETTIMEAT] , [COMMENTS] , [PREVRESULT] , [NEXTACTION] , [ACTIONON] , [CLOSEDATE] , [USERDEF01] , [USERDEF02] , [USERDEF03] , [USERDEF04] , [USERDEF05] , [USERDEF06] , [USERDEF07] , [USERDEF08] , [USERDEF09] , [USERDEF10] , [USERDEF11] , [USERDEF12] , [USERDEF13] , [USERDEF14] , [USERDEF15] , [USERDEF16] , [ULINKEDIN] , [UFACEBOOK] , [UTWITTER] , [UREP] , [UNAICS] , [UBRDETAIL] , [UCREATEON] , [UCDATE] , [USTATUS] , [UCREATEAT] , [UACCNO] , [ULASTUSER] , [ULASTDATE] , [URECNO] , [ULASTTIME] , [URECIDNO] , [UTEST] , [UHMAIL] , [UHMAILID] , [U] , [UROLAND] , [UTEST2] , [RECID] from [dbo].[CONTACT2]
    Com Error IDispatch error #3079
        HRESULT    : -2147217913
        Description: Conversion failed when converting date and/or time from character string.

    Or also

    GM version: 2016.1.0.188 R Thread ID 7244:

    ADO Error in AdoCommand::execute INSERT INTO [dbo].[GOLDMINETEMPCOPYTABLE] ( [ACCOUNTNO] , [CALLBACKON] , [CALLBACKAT] , [CALLBKFREQ] , [LASTCONTON] , [LASTCONTAT] , [LASTATMPON] , [LASTATMPAT] , [MEETDATEON] , [MEETTIMEAT] , [COMMENTS] , [PREVRESULT] , [NEXTACTION] , [ACTIONON] , [CLOSEDATE] , [USERDEF01] , [USERDEF02] , [USERDEF03] , [USERDEF04] , [USERDEF05] , [USERDEF06] , [USERDEF07] , [USERDEF08] , [USERDEF09] , [USERDEF10] , [USERDEF11] , [USERDEF12] , [USERDEF13] , [USERDEF14] , [USERDEF15] , [USERDEF16] , [ULINKEDIN] , [UFACEBOOK] , [UTWITTER] , [UREP] , [UNAICS] , [UBRDETAIL] , [UCREATEON] , [UCDATE] , [USTATUS] , [UCREATEAT] , [UACCNO] , [ULASTUSER] , [ULASTDATE] , [URECNO] , [ULASTTIME] , [URECIDNO] , [UTEST] , [UHMAIL] , [UHMAILID] , [U] , [UROLAND] , [UTEST2] , [RECID] )
     SELECT [ACCOUNTNO] , [CALLBACKON] , [CALLBACKAT] , [CALLBKFREQ] , [LASTCONTON] , [LASTCONTAT] , [LASTATMPON] , [LASTATMPAT] , [MEETDATEON] , [MEETTIMEAT] , [COMMENTS] , [PREVRESULT] , [NEXTACTION] , [ACTIONON] , [CLOSEDATE] , [USERDEF01] , [USERDEF02] , [USERDEF03] , [USERDEF04] , [USERDEF05] , [USERDEF06] , [USERDEF07] , [USERDEF08] , [USERDEF09] , [USERDEF10] , [USERDEF11] , [USERDEF12] , [USERDEF13] , [USERDEF14] , [USERDEF15] , [USERDEF16] , [ULINKEDIN] , [UFACEBOOK] , [UTWITTER] , [UREP] , [UNAICS] , [UBRDETAIL] , [UCREATEON] , [UCDATE] , [USTATUS] , [UCREATEAT] , [UACCNO] , [ULASTUSER] , [ULASTDATE] , [URECNO] , [ULASTTIME] , [URECIDNO] , [UTEST] , [UHMAIL] , [UHMAILID] , [U] , [UROLAND] , [UTEST2] , [RECID] from [dbo].[CONTACT2]
    Com Error IDispatch error #3079
        HRESULT    : -2147217913
        Description: Conversion failed when converting the varchar value 'fgh' to data type int.



    c. Certain columns are missing from the CONTACT2 table although they are defined in Tools > Configure > Custom fields (CONTUDEF), this is actually not a structural corruption by itself as this is the state when a new user defined field was created but still no rebuild performed. Why it is considered here also as a reason is primarily when the GoldMine Administrator forgets to rebuild the user defined fields related tables, other applications like GoldMine Mobile will appear with errors when opening a Contact record for example 'Error failed to get Contact list' and the related GMConnect.log states similar errors like 

    2015-05-10 16:20:17,423 - [ERROR] Thread id[39] - GoldMine.Models.ApplicationExceptions.ClientInformationException: Error, Run SQLcollation statements error.,
    System.Data.SqlClient.SqlException (0x80131904):Invalid column name 'UTEST'.
    Invalid column name 'UTEST2'.



    d.  Certain varchar columns in CONTACT2 table as redefined in Microsoft SQL longer (for example varchar(50) than in the CONTUDEF  (varchar 10) + there are values in the fields which are also longer than the GoldMine defined length

    This may happen when the fields were defined separately from within GoldMine and then Microsoft SQL Server. During a rebuild GoldMine will take care of this mismatch but can do this only when there are no values in the back end which need to be truncated. This is not allowed through the Rebuild and therefore the rebuild fails.

    An indicator for this mismatch between CONTACT2 and CONTUDEF is the following Event viewer log entry


    GM version: 2016.1.0.188 R Thread ID 6388:

    ADO Error in AdoCommand::execute INSERT INTO [dbo].[GOLDMINETEMPCOPYTABLE] ( [ACCOUNTNO] , [CALLBACKON] , [CALLBACKAT] , [CALLBKFREQ] , [LASTCONTON] , [LASTCONTAT] , [LASTATMPON] , [LASTATMPAT] , [MEETDATEON] , [MEETTIMEAT] , [COMMENTS] , [PREVRESULT] , [NEXTACTION] , [ACTIONON] , [CLOSEDATE] , [USERDEF01] , [USERDEF02] , [USERDEF03] , [USERDEF04] , [USERDEF05] , [USERDEF06] , [USERDEF07] , [USERDEF08] , [USERDEF09] , [USERDEF10] , [USERDEF11] , [USERDEF12] , [USERDEF13] , [USERDEF14] , [USERDEF15] , [USERDEF16] , [ULINKEDIN] , [UFACEBOOK] , [UTWITTER] , [UREP] , [UNAICS] , [UBRDETAIL] , [UCREATEON] , [UCDATE] , [USTATUS] , [UCREATEAT] , [UACCNO] , [ULASTUSER] , [ULASTDATE] , [URECNO] , [ULASTTIME] , [URECIDNO] , [UTEST] , [UHMAIL] , [UHMAILID] , [U] , [RECID] )
     SELECT [ACCOUNTNO] , [CALLBACKON] , [CALLBACKAT] , [CALLBKFREQ] , [LASTCONTON] , [LASTCONTAT] , [LASTATMPON] , [LASTATMPAT] , [MEETDATEON] , [MEETTIMEAT] , [COMMENTS] , [PREVRESULT] , [NEXTACTION] , [ACTIONON] , [CLOSEDATE] , [USERDEF01] , [USERDEF02] , [USERDEF03] , [USERDEF04] , [USERDEF05] , [USERDEF06] , [USERDEF07] , [USERDEF08] , [USERDEF09] , [USERDEF10] , [USERDEF11] , [USERDEF12] , [USERDEF13] , [USERDEF14] , [USERDEF15] , [USERDEF16] , [ULINKEDIN] , [UFACEBOOK] , [UTWITTER] , [UREP] , [UNAICS] , [UBRDETAIL] , [UCREATEON] , [UCDATE] , [USTATUS] , [UCREATEAT] , [UACCNO] , [ULASTUSER] , [ULASTDATE] , [URECNO] , [ULASTTIME] , [URECIDNO] , [UTEST] , [UHMAIL] , [UHMAILID] , [U] , [RECID] from [dbo].[CONTACT2]
    Com Error IDispatch error #3159
        HRESULT    : -2147217833
        Description: String or binary data would be truncated.

    the message resource is present but the message is not found in the string/message table


    The GoldMine administrator needs to take care of this mismatch by either if possible to extend the CONTUDEF length via Tools >> Configure >> Custom Fields >> Properties of the user defined field >> Adjust the length to the same length as in the back end (for example as above to 50 instead of 10) or if this is not possible (for example when the CONTUDEF is already set to the maximum 250 characters but MS SQL shows varchar(1000)) the GoldMine Administrator or SQL DBA needs to modify the length in the CONTACT2 table structure (in such a case it would be highly recommended to verify which and why there are longer values in the field than GoldMine allows as external import routines or similar may need to be adjusted too.)




    [CONCLUSION]
    - Such issues are considered as a structural data corruption. While day to day operations of users within GoldMine Premium Edition many times are still possible without any obvious disruption, especially related applications (like GoldMine Connect) are highly impacted and cannot be used or also new structural changes cannot be implemented anymore with the recommended way from within GoldMine. Therefore such mismatches need to be corrected as soon as they are discovered by GoldMine Administrator or the GoldMine Partner.
    - GoldMine Technical Support does provide the below queries and recommendations, so that the GoldMine Administrator or the GoldMine Partner can identify the mismatches and fix them but GoldMine Technical Support usually does not fix those issues directly on a customer's environment.  

    Identify  all fields in CONTACT2 that aren't in the CONTUDEF table (You can add  these via Tools > Configure > Custom Fields, pay attention  to the field type and length if you are planning on keeping the fields  for use)

    SELECT NAME FROM SYSCOLUMNS WHERE ID =(SELECT ID FROM SYSOBJECTS WHERE NAME= 'CONTACT2')AND NAME > 'U' AND NAME NOT IN
    (SELECT FIELD_NAME FROM CONTUDEF WHERE DBFNAME = 'CONTACT2' AND FIELD_NAME LIKE 'U%')

    Identify  all fields in the CONTUDEF table that are not in the CONTACT2 table  (These can be deleted from Tools > Configure > Custom  Fields, as they can be setup again when the behavior is not occurring anymore, alternatively as an unsupported but possible workaround they can be added to the CONTACT2 table as columns. Pay attention  to the field type and length!)

    SELECT FIELD_NAME,* FROM CONTUDEF WHERE FIELD_NAME NOT IN
    (SELECT NAME FROM SYSCOLUMNS WHERE ID =(SELECT ID FROM SYSOBJECTS WHERE NAME= 'CONTACT2')AND NAME > 'U')AND DBFNAME = 'CONTACT2' AND FIELD_NAME LIKE 'U%'

    -  Additionally it is absolutely strongly recommended to verify that the  data types of the fields are correct from within the GoldMine definition  (CONTUDEF) and the real column in the CONTACT2. Also here we can  provide a SQL query for your verification.

    Compare CONTACT2 - user defined fields Data_type definition in Microsoft SQL Server vs. CONTUDEF Field_Type definition

    SELECT S1.COLUMN_NAME, S1.DATA_TYPE,  S2.FIELD_NAME, S2.FIELD_TYPE FROM INFORMATION_SCHEMA.COLUMNS AS S1  JOIN CONTUDEF S2
    ON S1.COLUMN_NAME=S2.FIELD_NAME WHERE S1.COLUMN_NAME LIKE 'U%' ORDER BY
    S1.COLUMN_NAME

    [NOTE]
    - The following values are considered matches for Type:
    C = varchar
    D = datetime
    N = float, int, smallint, etc. (SQL numeric types)

    Compare and identify differences in varchar lengths of CONTACT2 and CONTUDEF

    -- For having an overview about varchar lengths full list
    SELECT S1.COLUMN_NAME, S1.DATA_TYPE, S1.CHARACTER_MAXIMUM_LENGTH,  S2.FIELD_NAME, S2.FIELD_TYPE, S2.FIELD_LEN FROM INFORMATION_SCHEMA.COLUMNS AS S1  JOIN CONTUDEF S2
    ON S1.COLUMN_NAME=S2.FIELD_NAME WHERE S1.COLUMN_NAME LIKE 'U%' and S1.DATA_TYPE = 'varchar' ORDER BY
    S1.COLUMN_NAME


    -- For identifying only mismatches between varchar lengths
    SELECT S1.COLUMN_NAME, S1.DATA_TYPE, S1.CHARACTER_MAXIMUM_LENGTH,  S2.FIELD_NAME, S2.FIELD_TYPE, S2.FIELD_LEN FROM INFORMATION_SCHEMA.COLUMNS AS S1  JOIN CONTUDEF S2
    ON S1.COLUMN_NAME=S2.FIELD_NAME WHERE S1.COLUMN_NAME LIKE 'U%' and S1.DATA_TYPE = 'varchar' and S1.CHARACTER_MAXIMUM_LENGTH <> S2.FIELD_LEN ORDER BY
    S1.COLUMN_NAME



    - Once  the data type and availability of the specific fields are corrected  also a rebuild should be possible without any further errors although it  might not be necessary to run an explicit rebuild of the FIELDS5,  CONTUDEF and CONTACT2 anymore -  especially after already experiencing this error in the past, it is specifically recommended to have a full running back up of your database prior doing  again a rebuild of the database or individual files


    - In case the original error appears again, the next step would be to  gather the related SQL trace while performing this action. As this will  usually show further details where the work flow is interrupted.

    Prepare  in GoldMine everything to perform the specific action, browse in  GoldMine to the record with which the demonstration will be made / the  behavior is replicable

     

    1. In SQL Server Management Studio

    2. Tools > SQL Server Profiler

    3. File > New Trace

    4. Trace Tab > Properties

    A. Use the template Standard (Default)

    B.  Save to file and select a destination (depending on the amount of  actions necessary you may consider to Enable the file rollover and set  the size to 5 MB)

    5. Event Tab > Selection (ONLY in case also other applications have access on the MS SQL Server)

    a. Filter Column > Application Name > Like > GoldMine Premium Edition > OK

    6. Run

    7. Perform in GoldMine only the specific actions.

    8. Stop the profiling via the red vcr stop icon.

     

    - Depending on your support options, provide the saved .trc file to your IT/GoldMine Administrator, your GoldMine Partner or GoldMine Technical Support and keep in mind that this should be compressed if the file size exceeds 5 MBs.