How can you convert/cast text datatypes from a PostgreSQL database to a linked MS SQL Server?

Question

Good morning/afternoon! Been working on this problem most of the day so I figured it was time to appeal to a larger audience.

I'm running Microsoft SQL Server 2012. I have created a "Linked Server" to a PostgreSQL server. When I try to issue a query to the PostgreSQL server I get this:

SELECT *
FROM OPENQUERY(MYDB, 'SELECT notes from remote_view LIMIT 50');

Msg 7347, Level 16, State 1, Line 1
OLE DB provider 'MSDASQL' for linked server 'MYDB' returned data that does not match expected data length for column '[MSDASQL].notes'. The (maximum) expected data length is 8000, while the returned data length is 9088.

If I truncate the field (using LEFT(notes, 4000)) I can get it to work. The field on the PostgreSQL table is the "text" data type.

Any ideas how to get the data to come across without losing any of it?

UPDATE #1:

Trying to cast the value to varchar(max) yields this:

SELECT *
FROM OPENQUERY(MYDB, 'SELECT cast(notes as varchar(max)) as notes2 from remote_view LIMIT 50');

OLE DB provider "MSDASQL" for linked server "QPID" returned message "ERROR: syntax error at or near "max";
No query has been executed with that handle".
Msg 7350, Level 16, State 2, Line 1
Cannot get the column information from OLE DB provider "MSDASQL" for linked server "QPID".

If I try to cast it as varchar(8000), it gives me this:

OLE DB provider "MSDASQL" for linked server "QPID" returned message "Requested conversion is not supported.".

Answer 1

I had the exact problem. I found a workaround, if length of your text data is not bigger than Postgresql's character varying size limit (10485760). You just need to change Postgresql ODBC MaxLongVarchar setting to 10485760 like the following screenshot as a first step.

After that you have three options:

• You can change your text data field to character varying (10485760) at PostgreSql Server, if it's possible. The following syntax will work without any problems.

  SELECT * FROM LINKEDSERVER.dbname.schemaname.tablename
  

• If you can't change original table, you can create a view on postgresql which transforms all text fields as originaltextfield::varchar(10485760) and select from postgresql view, instead of table

  SELECT * FROM LINKEDSERVER.dbname.schemaname.viewname
  

• You can use OPENQUERY as follows

  SELECT * FROM OPENQUERY(LINKEDSERVER, 'SELECT id, textfield::varchar(10485760) FROM schemaname.tablename')

Answer 2

Here's what I came up with that seemed to work for this issue. Break the main text field into several smaller VARCHAR fields, inside the OPENQUERY. To do this, CAST the field as a very large VARCHAR value. Then do LEFT/SUBSTRING of that value, to grab a small chunk of it. Outside of the OPENQUERY CAST each of the fields you created as VARCHAR(MAX) and concatenate them together. Using the original example, it would be something like this..

SELECT CAST(Notes1 AS VARCHAR(MAX)) 
       + CAST(Notes2 AS VARCHAR(MAX)) 
       ... 
       + CAST(Notes5 AS VARCHAR(MAX)) AS Notes
FROM OPENQUERY(MYDB, 'SELECT 
                        LEFT(CAST(Notes AS VARCHAR(20000),3500) AS Notes1
                       ,SUBSTRING(CAST(Notes AS VARCHAR(20000),3501,3500) AS Notes2
                       ...
                       ,SUBSTRING(CAST(Notes AS VARCHAR(20000),14004,3500) AS Notes5 
                       FROM remote_view')