Immediate series of error messages when opening the Forecast Dashboard, the Opportunity Dashboard or the Sales User Dashboard - Error converting data type varchar to float in the Microsoft Windows Event Log

Version 1

    Details

    Immediate series of error messages when opening the Forecast Dashboard, the Opportunity Dashboard or the Sales User Dashboard - Error converting data type varchar to float in the Microsoft Windows Event Log


    [STEPS TO REPLICATE]
    1. Go To >> Dashboards
    2.a. If previously one of the Dashboards Forecast Dashboard, the Opportunity Dashboard or the Sales User Dashboard was used and the error was already experienced
    >> RESULT: see below as RESULT for 2.b.


    2.b. When the Dashboards Forecast Dashboard, the Opportunity Dashboard or the Sales User Dashboard Dashboards were not yet used or reset (see related Knowledge Article # 18976 - How to reset a user's last used Dashboard? Where and what the setting would be for the last dashboard opened by a user?) >> Select one of the Sales section's dashboards
    >> RESULT:
    - A series of errors on the screen which may appear with different messages in most cases -

    Dashboard - Opportunity Dashboard, Error -

    CCustView::ReloadDataSource, data source failed


     

    Dashboard - Opportunity Dashboard, Error -

    CCustView::ReloadDataSource, data source falied

     

    - The Windows Event Log file reveals the underlying error message (please make sure to review all related Event Log entries)

    ADO Error in CApplicationData::Select code - SELECT  c1.CONTACT as [Primary_Opp/Proj_contact], c1.COMPANY as [Company], op.NAME as [Opp/Proj_name], op.STATUS as [Opp/Proj_status], op.STAGE as [Opp/Proj_stage], case SUBSTRING(op.RECTYPE,2,1) when ' ' then isnull( DATEDIFF( DAY, op.STARTDATE, GETDATE() ) +  1, -1 ) when 'Z' then isnull( DATEDIFF( DAY, op.STARTDATE, op.CLOSEDDATE ) + 1, -1 ) end as [Opp/Proj_Age], op.PRODUCTNAME as [Opportunity_Product], op.FORPROB as [Opportunity_probability], op.CLOSEAMT as [Opportunity_closed_amt], cast( op.F3 as float ) as [Opportunity_Units], op.STARTDATE as [Opp/Proj_start_date], op.CLOSEBY as [Opp/Proj_target_close_by_date], op.ACCOUNTNO as [ACCOUNTNO], op.recid as [recid], op.FORAMT as [FORAMT], op.USERID as [USERID]
    FROM dbo.OPMGR op left join( dbo.CONTACT1 c1 inner join dbo.CONTACT2 c2 on c1.ACCOUNTNO = c2.ACCOUNTNO ) on c1.ACCOUNTNO = op.ACCOUNTNO 
    WHERE (op.RECTYPE like '_ %' OR op.RECTYPE like '_Z%') AND (  op.RECTYPE like 'O%' )                
    ORDER BY [Company] asc
    Com Error IDispatch error #3079
        HRESULT    : -2147217913
        Description: Error converting data type varchar to float.

     


    Resolution

    [CAUSE]

    - The errors are  most likely caused by *Bad* data in the Opportunity manager, Forecasted Sales or completed Sales (Depending on which Dashboard the error is experienced)
    - One or more of the values in the related table fields has an invalid character (anything other than a numeric value).

    [RESOLUTION]

    - Verify and correct where applicable the corrupt/invalid data from the specific table/fields. Please keep in mind that the below used Microsoft SQL function is only available from Microsoft SQL Server 2008 and higher.

    The below SQL expression should help in determining what records have invalid characters

    -- For Forecasted Sales in the CAL table >> Pending Sales
    select * from cal where isnumeric(number1)<>1 and RECTYPE = 'S'
    Select * from cal where isnumeric(number2)<>1 and RECTYPE = 'S'


    -- For Opportunities in the OPMGR table
    select  * from OPMGR where isnumeric(F1)<>1 and F1 <> '' and  rectype like 'O%' and rectype not in ('OC', 'OI',  'OK', 'OP',  'OT','OX')
    select * from OPMGR where isnumeric(F2)<>1  and F2  <> '' and rectype like 'O%' and rectype not in ('OC', 'OI',  'OK',  'OP', 'OT','OX')
    select * from OPMGR where isnumeric(F3)<>1   and F3 <> '' and rectype like 'O%' and rectype not in ('OC',  'OI',  'OK', 'OP', 'OT','OX')
    select * from OPMGR where  isnumeric(FORAMT)<>1  and rectype like 'O%' and rectype not in  ('OC', 'OI',  'OK', 'OP', 'OT','OX')
    select * from OPMGR where  isnumeric(FORPROB)<>1  and rectype like 'O%' and rectype not in  ('OC', 'OI',  'OK', 'OP', 'OT','OX')
    select * from OPMGR where  isnumeric(CLOSEAMT)<>1  and rectype like 'O%' and rectype not in  ('OC', 'OI',  'OK', 'OP', 'OT','OX')


    -- For Completed Sales in the CONTHIST table
    select * from CONTHIST where isnumeric(DURATION) <> 1 and SRECTYPE = 'S'
    select * from CONTHIST where isnumeric(UNITS) <> 1 and SRECTYPE = 'S'


    NOTE:
    GoldMine Technical Support services cannot aid in troubleshooting data related issues.  For data troubleshooting, GoldMine Technical Support recommends that you engage with your solutions partner to troubleshoot data consistency issues.