How to verify that your GoldMine system might be affected by ANSI_Padding_Off setup columns?

Version 1

    Details

    [INFORMATION]
    A  {SPACE} (blank)  character
    / trailing space is interpreted by Microsoft SQL Server depending on the ANSI  PADDING ON or OFF setting for the database the Microsoft SQL Server differently. This may lead in the underlying queries to an 'incorrect' or undesired result as a value 'x' and a value 'x  ' is not considered as the same value.


    -  Since Microsoft SQL Server 2005 and higher the  default setting is always ANSI PADDING ON, so when you installed  GoldMine for the first time on Microsoft SQL 2005 and higher you should  not be impacted unless a DBA actively changed this setting
    - Microsoft SQL  Server 2000 and lower had a default setting of ANSI PADDING OFF for the  created databases. This means that usually only GoldMine environments  upgraded from this versions of Microsoft SQL Server are impacted.

    [SYMPTOMS]
    - On certain actions you don't get the expected amount of results e.g. within GMME you are getting less active opportunities than in GMPE
    - Calendar colors may change unexpectedly without having changed them
    - further undesired behavior not yet known but may appear

    [VERIFICATION]
    in a SQL Profile you recognize that GoldMine queries for values like 'O  ' (only blanks at the end), or 'P %' (at least one further blank) or also 'B4111974098$UU%A>   ' (e.g. for an ACCOUNTNO where no Contact was entered). Although this does not necessarily means that the current environment is impacted by any ANSI_PADDING_OFF set columns it is a good indicator.

    We strongly recommend that you contact also GoldMine Technical Support with your recognition, see also below



    Resolution

    [RESOLUTION]
    The following workflow is provided only as-is without any warranty of any kind. Please make sure to have a full running backup of the GoldMine database before adjusting the columns and also keep in mind that this change is not sync aware and it is necessary to apply the same steps on any remote sites or undocked user's GoldMine systems if applicable

    If you are not affected by a confirmed behavior (please verify the following Knowledge Articles), please make sure to contact GoldMine Technical Support for verification before simply trying to adjust a customer's live environment.  

    - Knowledge Article # 15081 - When reviewing opportunities in GoldMine Mobile (GMME) not all open opportunities for the user are displayed compared to GoldMine Premium Edition.
    - Knowledge Article # 14195 - When scheduling activities and assigning a color other activities are changed in the graphical calendar to the same color although they should be blue, the same happens when completing activities and they are displayed in the graphical calendar

    Generic Steps for identifying any possible affected columns in a GoldMine table and how to change them

    A. Identification:


    - verify via the following statement the ANSI_PADDING setting for each  column for the certain table.


    SELECT
      Name,Is_ANSI_Padded
    FROM    sys.columns
    where object_Name(object_ID) = 'Table1'

    (replace Table1 with the desired table name)

    Keep in mind that ANSI_Padding is always ON for unicode databases and their related nvarchar data type fields

    When ANSI_PADDING is set to ON (shows as 1):
    ·         CHAR and BINARY (NULLable and non-NULLable) columns  are filled with blanks or zeros to the length of the column.
    ·         Trailing blanks and zeros in VARCHAR and VARBINARY  columns are not trimmed, but the values are not padded to the end of the  column.
    When ANSI_PADDING is set to  OFF (shows as 0):
    ·         Non-NULLable CHAR and BINARY columns are filled  with blanks or zeros to the length of the column.
    ·         NULLable CHAR, BINARY, VARCHAR, and VARBINARY have  zeros and blanks trimmed and are not padded to the end of the  column.


    B. change impacted columns: 

    The following workflow is provided only as-is without any warranty of any kind. Please make sure to have a full running backup of the GoldMine database before adjusting the columns and also keep in mind that this change is not sync aware and it is necessary to apply the same steps on any remote sites or undocked user's GoldMine systems if applicable

    All users should be logged out of GoldMine as also all related services and integrations should be shut down/closed during this change


    1. In the SQL Server Management Studio browse to the certain table and further to the Indexes
    for each index name starting make sure to have either a documentation for them, otherwise
    a. Right click on the index >> Script Indexes >> Create To >> New Query Editor Window
    b. repeat a. for every index
    2. Delete all indexes via
    a. Right Click >> Delete >> OK
    b. Repeat for all available Indexes in the certain table
    3. usually a simple Alter Table <Table> Alter  Column <Column> statement with the ANSI_PADDING ON set on the connection should change the setting.
    3.a. in SQL Server Management Studio make sure to set  ANSI_PADDING ON via

    Set  ANSI_PADDING ON;

    3.b. run the alter statement for each of the  columns which needs to be changed:

    Alter Table Table1
      Alter Column column varchar(x)
     

    Please make sure to have the identical data type set and also the identical length of the original column

    4.start GoldMine Premium Edition in maintenance mode via
    4.a. on the log on window enter a user and password with master rights
    4.b. make sure to press the CTRL key at the same time when pressing OK
    5. Button Maintain (on the bottom left)
    6. Individual Files
    7. Select the table you changed
    8. only re-index the table (so leave everything else as default)
    9. Re-apply any 'user defined' indexes which were not already recreated  by GoldMine as these indicate that your system was using user defined  indexes e.g. via executing one query after the other from step 1.a. and 1.b.