Argument data type varchar is invalid for argument 2 of like function error when searching for notes via SQL query within GoldMine

Version 1

    Details

     

    "Argument data type varchar is invalid for argument 2 of like function" error when searching for notes via SQL query within GoldMine. 

    The following query was run:

    select * from contact1 as c1, Cal where c1.accountno=Cal.accountno and cal.notes like 'TEST'

     


    Resolution

     

    [CAUSE]
    Most Notes fields within GoldMine are stored since GMPE 8.5.x in the SQL database as an image field data type, which means it cannot be used in a query as a character field or a text field. In order to query for a certain string in the Notes field using a where clause the image field needs first to be converted into a readable format.

    This can be done with the SQL CAST clause, for further information about data type conversion, refer for example to https://msdn.microsoft.com/en-us/library/ms187928.aspx

    [RESOLUTION]

    The correct query for the above example is as follows:

    select * from contact1 as c1, Cal where c1.accountno=Cal.accountno and CAST(CAST(CAL.NOTES AS varbinary(MAX)) AS varchar(MAX)) like '%TEST%'

    Additional Note:
    As this may impact not only SQL Queries within GoldMine but also for example Crystal Reports or other reporting or own data exchange integrations, please review also Knowledge Article # 14442 :  Certain data like contact notes and Email bodies appear in Crystal reports as encrypted, how can those be decrypted?