Failed to execute command: insert into {{owner}}Environment values( 'GMFileLocation',",",",'O',0,'GSPDONW&(<S(W<'); Because: Conversion failed when converting the varchar value 'GSPDONW&(<S(W<' to data type smallint after upgrade to 2013.1, 2014.1 or 201

Version 1

    Details

     

    After upgrading GoldMine Premium Edition (GMPE) to to 2013.1, 2014.1 or 2014.2 and launching GoldMine the first time after upgrading the following error appears when the database is revised

    Failed to execute command: insert into {{owner}}Environment values( 'GMFileLocation',",",",'O',0,'GSPDONW&(<S(W<'); Because: Conversion failed when converting the varchar value 'GSPDONW&(<S(W<' to data type smallint.  What does this error mean and how can it be fixed?

     


    Resolution

    [CAUSE]

    GoldMine Premium Edition (GMPE) 8.0.x does not have a SYNC column in the ENVIRONMENT table as this column was added in GoldMine Premium Edition (GMPE) 8.5.0.98 and higher and was added in the table design as the last column.

     

    Any customers who were not forced to maintain their database with rebuild and pack the tables option (including ENVIRONMENT table) from within GoldMine and then perform an upgrade from such a version following the documented upgrade instructions will have the SYNC column added as the last column in the ENVIRONMENT table which means the structure appears like below.

      vs.

     

    As the database revision script contains the values to be inserted in the default column structure of the ENVIRONMENT table the varchar value GSPDONW&(<S(W<' (which should be inserted into the RECID field) cannot be converted into a smallint value which would be required for the last column SYNC. Therefore the error message appears.


    [RESOLUTION]


    GoldMine Support Team strongly recommends to verify upgrades within a test environment, the issue should be experienced already in this phase, which means there are 2 alternatives available as a resolution. 

    To fix the behavior on an already upgraded production system only alternative A is available and the steps should be applied on the production system.  

    Alternative A - adjust already upgraded test / production environment:

    I. Ignore error message
    II. verify the order of the columns in the ENVIRONMENT table

    - Make sure to have a full running Database backup and that all users are logged off from GoldMine and related services / applications

    a. Either Run a Pack and rebuild on the Environment table before you upgrade.
    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 ENVIRONMENT 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. verify that the ENVIRONMENT->SYNC column is now before ENVIRONMENT->RECID (last one) column in the SQL Server Management Studio


    b. Or you can manually adjust the Column in the ENVIRONMENT table in SQL Management Studio

    1. Backup the GoldMine SQL database
    2. Open SQL Management Studio
    3. Tools > Options > Designers
    4. Make sure the CheckBox for Prevent saving changes that require table re-creation is unchecked.
    5. Expand the GoldMine database
    6. Right Click the ENVIRONMENT table and click Design
    7. Move the SYNC field so that is it second to last, above the RECID field
    8. Verify the RECID is the last field/column
    9. Save the Changes

    III. Manually add the value in the ENVIRONMENT table by submitting the following query in the SQL Server Management Studio on the GoldMine database

    Insert into Environment values('GMFileLocation', '', '', '', 'O', 0, 'GSPDONW&(<S( W<')


    Alternative B - only available for test upgrades on separate test environments > re-do the test upgrade with prior adjusting the ENVIRONMENT table

    I. Trash the actual test upgrade environment for example when this was performed on a virtual environment
    II. Prepare the test environment as before the prior upgrade attempt (means original GoldMine Premium Edition version and backup of production database)
    III. verify the order of the columns in the ENVIRONMENT table 

    a. Make sure to have a full running Database backup and that all users are logged off from GoldMine and related services / applications
    b. Either Run a Pack and rebuild on the Environment table before you upgrade.
    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 ENVIRONMENT 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. verify that the ENVIRONMENT->SYNC column is now before ENVIRONMENT->RECID (last one) column in the SQL Server Management Studio


    b. Or you can manually adjust the Column in the ENVIRONMENT table in SQL Management Studio

    1. Backup the GoldMine SQL database
    2. Open SQL Management Studio
    3. Tools > Options > Designers
    4. Make sure the CheckBox for Prevent saving changes that require table re-creation is unchecked.
    5. Expand the GoldMine database
    6. Right Click the ENVIRONMENT table and click Design
    7. Move the SYNC field so that is it second to last, above the RECID field
    8. Verify the RECID is the last field/column
    9. Save the Changes
    IV. Perform again the test upgrade and this time the error should not occur again

    - Before upgrading the production environment it is absolutely recommended to follow alternative B with prior adjusting the ENVIRONMENT table
     
    Additional Information:
    Please refer to Knowledge Article # 11300 for further information about this entry in the ENVIRONMENT table