How can you convert/cast text datatypes from a PostgreSQL database to a linked MS SQL Server?
postgresql-9.1, sql, sql-server
Solution
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')
Problem
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.". ```