7

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.

Thom A
  • 88,727
  • 11
  • 45
  • 75
Gabriele D'Onufrio
  • 405
  • 1
  • 5
  • 17

2 Answers2

4

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.

Dimitris Tsitsamis
  • 41
  • 2
2

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.
Đức Trí Bùi
  • 46
  • 3
  • 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). – andyabel Jan 10 '22 at 20:07