Error when try to apply Industry Template >> Financial Services - Database operation error: Cannot insert the value NULL into column SFIELD1, table GoldMine.dbo.REPORT32: column does not allow nulls INSERT fails.

Version 1

    Details

    Error when try to apply Industry Template >> Financial Services - Database operation error: Cannot insert the value NULL into column SFIELD1, table GoldMine.dbo.REPORT32: column does not allow nulls INSERT fails.

    [STEPS TO REPRODUCE]
    1. Make sure to have a full running backup of your GoldMine database!
    2.
    In GoldMine with master rights

    3. Tools >> Quick Start Wizard

    4. Select the 3rd icon >> The Industry Templates Wizard will allow you ...


    5. From the Customization Installer select from the drop down list >> Financial Services >> Next


    6. Review the information for the GoldMine Financial Services Template >> Next

    7. Select the desired and Actions from Additions/Updates and if applicable Deletions (in order to replicate the behavior the absolute minimum is to select GoldMine Reports) >> Next


    8. Confirm that you understood that changes cannot be undone with entering YES into the box >> Next


    >> RESULT:

    During the process an error appears:
    Database operation error: Cannot insert the value  NULL into column SFIELD1, table GoldMine.dbo.REPORT32: column does not  allow nulls. INSERT fails.


     


    Resolution

    [REASON/CAUSE]

    - The reason is that the specific GoldMine database was upgraded from an older originally older version of GoldMine (verified for example from GM 6.7 >> GMPE 9.0.x >> GMPE 2014.2/GMPE 2015.1 but maybe also other later versions like GM 7.x or GM 8.x may result in the same behavior)
    - While a fresh installation of recent versions set the specific (and also other fields in the REPORT32 table) to ALLOW NULLS the original field definition was at one point to NOT ALLOW NULLS. During the upgrade through the versions this field definition for the REPORT32 table was not changed which means that also in the current version in the current GoldMine database the REPORT32 table still does NOT ALLOW NULLS for the SFIELD1 (and certain other fields)

    - Upgraded from GM 6.7 >> GMPE 9.0.x >> GMPE 2014.2/GMPE 2015.1



    - Fresh installation of GMPE 2014.2/2015.1


    [RESOLUTION]
    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. 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 REPORTS32 table 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 + 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

    12. Recommended:

    a. Either verify via user interface in SQL Server Management Studio >> browse to GoldMine database >> Browse to Tables >> Browse to REPORT32 >> Browse to Columns >> Verify that SFIELD1 shows now as SFIELD1 (varchar(20),null)




    b. Or via SQL Query in SQL Server Management Studio or even within GoldMine via Tools >> SQL Query with the following query that column SFIELD1 shows as Yes in column is_Nullable

     

     

    SELECT Table_catalog, Table_name, COLUMN_NAME , Is_nullable 

    FROM INFORMATION_SCHEMA.COLUMNS

    WHERE (TABLE_NAME = 'REPORT32')



     

    13. Re-test the behavior and verify that the Industry template >> Financial Service can be now applied via the Quick Start Wizard 

     

    Please keep in mind that we strongly recommend to have a full running backup prior applying any templates via the Quick Start Wizard!