SQL error: The (maximum) expected data length is 510, while the returned data length is 1010

Question

I have a data source from Cloudera Hive ODBC in MS SQL Server connected via a linked server. When I try to query one of the tables it throws an error as follows ' OLE DB provider 'MSDASQL' for linked server 'CL' returned data that does not match the expected data length for column 'referralreason'.The (maximum) expected data length is 510, while the returned data length is 1010.' I tried to cast it to varchar, changed the connection string criteria by adding OLEDBReturnCharAsWChar=0, tried using only open query no luck, some examples I tried are below:

1. SELECT CAST(Notes1 AS VARCHAR(MAX))

   • CAST(Notes2 AS VARCHAR(MAX))
   • CAST(Notes3 AS VARCHAR(MAX)) AS Notes FROM OPENQUERY(CL, 'SELECT LEFT(CAST(referralreason AS VARCHAR(1000), 400) AS Notes1 ,SUBSTRING(CAST(referralreason AS VARCHAR(1000),401,800) AS Notes2 ,SUBSTRING(CAST(referralreason AS VARCHAR(1000),801,1200) AS Notes3 FROM from REFERRAL')

2. declare @my varchar(max) set @my = 'select * from [REFERRAL]' execute (@my) AT [CL]

3. select * from OPENQUERY(CL, 'select CAST(referralreason AS NVARCHAR(4000)) as refreason from REFERRAL')