1

I am running a query from SQL Server 2019(64bit) SSMS to PostgreSQL on AWS through Linked Server. Other queries are working fine but this particular query is failing due to message column. My query is as follows:

 SELECT * from OPENQUERY (ABC, 'SELECT id, 
                                 old_message,
                                 message
                                 FROM actions;') 

Error Message:

OLE DB provider 'MSDASQL' for linked server 'DRIQ' returned data that does not match expected data length for column '[MSDASQL].message'. The (maximum) expected data length is 1400, while the returned data length is 1510.

Query is working fine without message column. But when I add message column, it start giving me an above error. The datatype of message column is JSON in postgres db. For ODBC, I am using 64bit Unicode(x64) and compatibility level of my db is 150. So how to get the data of message(json) column in sql server 2019?

  • 2
    It seems PostgresSQL is sending more data than SQL Server is expecting - if it were me I'd be trying to use a PostgresSQL function to convert the data column to something wider (ie the equivalent of Cast or Convert) so that SQL doesn't choke on the returned data. I've never used Postgres so you´ll have to research the exact syntax. – Mo64 Jul 21 '20 at 7:19
  • 2
    Does it work if you cast it as text? e.g. message::text or maybe a large varchar? message::varchar(8000)? – a_horse_with_no_name Jul 21 '20 at 9:54
0

Try with a different driver?

SQL Server and PostgreSQL Linked Server Configuration

You might also want to try implicitly casting the columns to tsql datatypes, i.e.

SELECT CAST(Id AS INT) AS Id, CAST(old_message AS NVARCHAR(MAX)) AS old_message, CAST(message AS NVARCHAR(MAX)) AS message FROM OPENQUERY...

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.