OLE DB provider 'for linked server returned data that does not match expected data length for

Question

I get an error querying a remote postgresql server from my sql server 2017 Standard via a linked server

this is the query:

    SELECT CAST(test AS VARCHAR(MAX))  FROM  OpenQuery(xxxx, 
     'SELECT  corpo::TEXT as test From public.notification')

and this is the error message:

    Msg 7347, Level 16, State 1, Line 57
    OLE DB provider 'MSDASQL' for linked server 'xxx' returned data that                 does not match expected data length for 
    column '[MSDASQL].test'. The (maximum) expected data length is 1024,         while the returned data length is 7774.

Even without conversions the error stills

For the odbc and linked server I followed this handy guide.

Bebebebeerine

can anyone help me please? I think tha the problem is the driver configuration or the driver itself: psqlodbc_x64.msi – Bundle

nobody?can anyone help me please? – Bundle

Answer 1

Can you try this?

SELECT  *
FROM    OPENQUERY(xxxx, '\
SELECT  TRIM(corpo) AS test
FROM    public.notification;
') AS oq

1. I prefer using OPENQUERY since it will send the exact query to the linked server for it to execute.
2. MySQL currently has problem with casting to VARCHAR data type, so I using TRIM() function to cheat it.

Callery

That's exactly the MySQL I'm running, but I'm still getting this error message: OLE DB provider 'MSDASQL' for linked server 'XXXX' returned data that does not match expected data length for column '[MSDASQL].trim(Address1)'. The (maximum) expected data length is 70, while the returned data length is 72. I think the reason is that my data has two accented characters (the default charset is utf8). – Numidia

Answer 2

In my case, I was reading the data through a view. Apparently, the data size of one column was changed in the underlying table but the view still reported to the linked server the original smaller size of the column. The solution was to open the view with MSSMS and save it again.

Radiomicrometer

Exactly the same for me. Thanks Dimitris. – Radloff

It worked for me too. It got fixed when I altered the view with the same definition again on the source server side. Thank you. – Minnesota

Answer 3

I'm running MS SQL Server 2019 on a Windows Server 2019 Standard OS. I also have the latest Simba Spark ODBC driver and created a linked server to it that works great. However we have a table that holds memos. The System and Manual text of this table are defined as a String in Azure Databricks and can contain thousands of characters. Every attempt, based on every suggestion I've read failed when pulling long strings from Azure Databricks. Until I came up with this segmentation query. Break up the Column text using a SUBSTRING command then stitch it back together. It looks like this:

SELECT ID, TXTPART1 + TXTPART2 + TXTPART3 AS MEMO_TXT
FROM OPENQUERY([LinkedServerName], 'SELECT ID, 
SUBSTRING(<FIELD NAME>, 1, 255) AS TXTPART1,
SUBSTRING(<FIELD NAME>, 256, 255) AS TXTPART2,
SUBSTRING(<FIELD NAME>, 511, 255) AS TXTPART3
FROM TABLE_NAME
WHERE <whatever>
')

In my case, I took it out 33 parts to capture 8,161 possible characters. It works flawlessly. Yes, it's a pain and ugly, but functional and provides a work around to the issue many of us are facing until both Microsoft and Simba fix the issue. I hope this helps anybody struggling with the MSDASQL issue.

Fringe