Error: Failed to drop table CONTACT2 when rebuilding the database after creating a new user defined field

Version 1

    Details

    Error: Failed to drop table CONTACT2 when rebuilding the database after creating a new user defined field


    Resolution

    [CAUSE]
    A process is locking the specific table, this might be a trigger, a replication, a logged user or from any other integration

    [RESOLUTION]
    - GoldMine needs exclusive access to the specific table in order to drop the table and recreate it
    - It is absolutely recommended to make sure that GoldMine has exclusive access to the table which means an IT administrator and or Microsoft SQL DBA should identify the reason why the table cannot be dropped, but in case it is not possible to identify the reasons or currently not to stop any interfering process the below workaround can be implemented. Again it is always preferred to go through the normal process of creating a user defined field and only on exceptional cases to try and apply the workaround.

    - Helpful for identification of blocking processes but not supported by GoldMine Technical Support and we cannot assist in troubleshooting, identifying or adjusting might be the following script which identify current sessions and processes


    SELECT  spid,
            sp.[status],
            loginame [Login],
            hostname,
            blocked BlkBy,
            sd.name DBName,
            cmd Command,
            cpu CPUTime,
            physical_io DiskIO,
            last_batch LastBatch,
            [program_name] ProgramName  
    FROM master.dbo.sysprocesses sp
    JOIN master.dbo.sysdatabases sd ON sp.dbid = sd.dbid where sd.name = 'GoldMine database'
    ORDER BY spid

    [WORKAROUND]
    Again it is always preferred to go through the  normal process of creating a user defined field and only on exceptional  cases to try and apply the workaround.

    - Pre-requisit: The field should be already configured and available within GoldMine via Tools >> Configure >> Custom Fields



    1. Make sure to have a full running backup of the GoldMine database
    2.  Make sure that all users are logged out all services and integration  are shut down (you may ever verify if there are any triggers or 3rd  party add-ons looking for information from CONTACT2 table)
    3. Open  the MS SQL Server Management Studio (any versions MS SQL 2008 and higher require first to change in the Option to make designer changes)
    4. In the SQL Server Management Studio >> Tools >> Options >> Designers >> Table and Database Designers
    5. Make sure to UNCHECK Prevent saving changes that require table re-creation


    6. OK
    7. Browse to the GoldMine database
    8. Browse the CONTACT2 table (of course making sure that you are in the correct contact set database)
    9. Right Click on the CONTACT2 >> Design


    10.  Scroll to the bottom and between the last user defined field and the RECID should be always the last column)>> Right Click>>  Insert Column


    11. Make sure that the
    a. NAME is identical to the defined FIELDNAME within GoldMine
    b. Make sure that the field types are matching
    Character in GoldMine >> varchar or nvarchar within MS SQL (then also length must match)
    Numeric in GoldMine >> float within MS SQL
    Date in GoldMine >> datetime within MS SQL 


    12. If applicable repeat the steps 10. and 11 for any further fields which need to be added
    13. At the top save the changes via the disc symbol


    14. Start GoldMine and verify that the field is now available and not displayed as N/A anymore



    15. Make sure to change the setting from step 4. and 5. back as this weakens otherwise MS SQL