Immediate series of error messages when opening the Dashboard Activity Summary Dashboard

Version 1

    Details

    - Immediate series of error messages when opening the Dashboard Activity Summary Dashboard

    [STEPS TO REPRODUCE]
    For example
    1. Go To > Dashboards or from bottom left Navigation pane to Dashboards)
    2. Management > Activity Summary Dashboard

    Alternatively this also happens when the user is just starting GoldMine as GoldMine will keep the last opened Tabs (Dashboards) and also the real last opened Dashboard itself.







    - The related Windows Event Logs shows related events shows errors Description: Incorrect syntax near the keyword 'AND'.
     
    While the SQL Query part might be different the offending part can be usually identified as WHERE 1=1    AND         AND in line 6


    ADO Error in CApplicationData::Select code - SELECT  [Activity_owner], [1] as [January], [2] as [February], [3] as [March], [4] as [April], [5] as [May], [6] as [June], [7] as [July], [8] as [August], [9] as [September], [10] as [October], [11] as [November], [12] as [December]
    FROM (
    SELECT ca.USERID as [Activity_owner], DATEPART( month, ca.ONDATE ) as [_PIVOT_FIELD_], ca.recid as [_PIVOT_BASE_]
    FROM dbo.CAL ca left join dbo.OPMGR op on ca.LOPRECID = op.RECID left join dbo.CASES c on ca.LOPRECID = c.RECID left join ( dbo.CONTACT1 c1 inner join dbo.CONTACT2 c2 on c1.ACCOUNTNO = c2.ACCOUNTNO ) on c1.ACCOUNTNO = ca.ACCOUNTNO 
    WHERE 1=1    AND         AND   DATEPART( year, ca.ONDATE ) = 2016   AND  case when ca.RECTYPE = 'Q' and ca.ACCOUNTNO like 'QS%' then NULL else ca.RECTYPE end in ( 'A', 'B', 'C', 'D', 'E', 'F', 'I', 'K', 'L', 'M', 'O', 'Q', 'S', 'T')
    ) p PIVOT (
     count(_PIVOT_BASE_) for [_PIVOT_FIELD_] in ([1], [2], [3], [4], [5], [6], [7], [8], [9], [10], [11], [12])
    ) as pvt 
    ORDER BY [Activity_owner] asc
    Com Error IDispatch error #3092
        HRESULT    : -2147217900
        Description: Incorrect syntax near the keyword 'AND'.



    Resolution

    [CAUSE]
    - This might be due to different reasons, with either one or more matching conditions BUT can be also caused by a also 'simple' orphaned records in the FILTERS table

    [INFORMATION]

    -  The main things in common whenever this behavior is experienced are
    a. A blank filter name in the GoldMine database > table FILTERS table > field NAME
    b. A blank OR missing '%SQL_where%' clause in the GoldMine database > table FILTERS table > field QEXPR


    [STEPS TO RESOLVE]

    -  Especially for the Deleting action it is absolutely recommended to make  sure to have a full running backup of the GoldMine database!!


    A. Try to identify the affected filters


    1. In SQL Server Management Studio
    2. Browse to the GoldMine Database
    3. New query Button
    4. Copy and paste the following query
     
    select * from FILTERS where RECTYPE = 'F' and NAME ='' and QEXPR not like '%SQL_where%'

    5. Execute (Red Exclamation mark)

    Verify if the results need to be reviewed or documented prior any further action

    B.  While Filters are usually not crucial and can be rebuild on demand and  especially it is not expected to have hundreds of results for the above query the GoldMine Administrator or MS SQL DBA can proceed already by deleting these results and to verify again especially as  such 'invalid' filters could not be used anyway within the system.

    Make sure to have a full running backup of the database !

    1.  Adjust the above query into a Delete and execute again (make sure that  the result is the identical than with above select statement)

    delete from FILTERS where RECTYPE = 'F' and NAME ='' and QEXPR not like '%SQL_where%'
     
    C.  Verify the behavior again

    1. Go To > Dashboards or from bottom left Navigation pane to Dashboards)
    2. Management > Activity Summary Dashboard

    - If the error or a different error occurs again make sure to verify above steps, if a different error occurs make sure to review the Event logs if also here the underlying error is still the same
    - In case the same or further errors are experienced which need to be investigated further, it is necessary to reset the dashboard for the user. The following steps will reset the loaded dashboards for the user.
    - For further information about resetting a user's dashboard, please refer also to the related Knowledge Article #18976

    - Open the the SQL Server Management Studio
    1. Browse to the GoldMine database
    2. New Query Button
    3. Make sure that the database drop down is set to the GoldMine database, if not already set
    4. Copy and paste and adjust the following queries into the query pane

    Delete from Environment where NAME='DashContainer' and USERID='MASTER' (replace with specific GoldMine Username)'

    While technically not necessary, it is recommended also to delete any related Hash values for dashboards

    Delete from Environment where NAME like 'Dash-%' and USERID='MASTER' (replace with specific GoldMine username

    5. Execute the query
    6.  Verify the result in GoldMine for the specific user and the Dashboard feature should open without any dashboard loaded