linked-serversql-server-2017

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

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.

Solution

  • 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.