OLEDB varchar max not working

Question

Having issues with OLEDB when writing a SQL Query that compares value with VARCHAR(MAX) column for inclusion. I'm wondering if the way that OLEDB connects to a database causes the issue or if it's the adapter itself.

Edit:

The query below by example gives an error when using the OLEDB driver that I'm still working to capture as the OLEDB driver isn't returning the error directly.

OLEDB driver version 18.6.7

Methods tried:

SELECT
*
FROM TABLE
WHERE
1=1
AND VARCHAR_MAX_FIELD = 'VALUE'

also tried

SELECT
*
FROM TABLE
WHERE
1=1
AND CONVERT(VARCHAR(4000), VARCHAR_MAX_FIELD) = 'SMALLER THAN 4000 CHAR VALUE THAT SHOULD WORK'

Within the query above also tried were TRY_CAST, TRY_CONVERT and CAST.

Answer 1

there is nothing wrong with the original query. It probably is not representative of the actual oledb query. please identify the sql versions and the actual oledb code.