Sql-server – Return text from PostgreSQL (9.6) to SQL Server from linked server query

linked-serverodbcpostgispostgresqlsql server

I am trying to get text (15002 characters long) from PostgreSQL using a linked server from SQL Server 2012, using the official PostgreSQL ODBC driver in Windows.

I try to pull data from Postgres using the following query:

create table #temp
(id int, g nvarchar(max) ) 
insert into #temp 
exec ('select id ,st_astext(geom) from spatialdata') at pg96odbc

This returns correct length data, but the text is truncated to ~4000 characters; the rest of the row contains null values. I see this using the following query (on SQL Server):

select id, g from #temp for xml auto

This works correctly in the other direction (Postgres pulling from SQL Server) using odbc_fdw and the SQL Server native driver.

Question: Is this a bug, or is there an ODBC/SQL Server option to get it to return all data, or a way to return varbinary(max) from the Postgres server?

I'm using BigSQL PostgreSQL 9.6, the latest ODBC driver for PostgreSQL 9.6, and the latest odbc_fdw.

FOR XML AUTO returns data containing this: (&\#x0; = null). Length of string is more or less correct, source geom is linestring.

MaxVarcharsize = -4:

Cannot get the current row value of column "[STREAM].geos" from OLE DB provider "STREAM" for linked server "(null)". [COLUMN_NAME= geos STATUS=Unknown DBBINDSTATUS]

MaxLongVarcharSize = 32k or 8k

(first "working character string is 4000 after it comes null values up to "real" length of text) example output below:

LINESTRING(2.773577 3.849574,2.773598 ... ,2.603447 3.847�����������������������������������������������������������������������������ꪝ������⎮���
LINESTRING(2.905497 0.547610999999996,2.905621 0.547591999999995,...,2.894462 0.5�����������������������������������������������������������������������������竿������ᔺ���
LINESTRING(3.608147 2.496786,3.607717 2.496774,3.607176 2.496804,3.605301 2.497387,3.603758 2.49786999999999,3.602421 2.49828599999999,3.600669 2.49880499999999,3.59832 2.49952,3.596655 2.500024,3.594704 2.500643,3.592416 2.50134899999999,3.590898 2.50182599999999,3.590145 2.50205199999999,3.589244 2.502335,3.588036 2.502623,3.586846 2.50291299999999,3.584997 2.503305,3.583173 2.503676,3.581646 2.503925,3.580145 2.50402699999999,3.578866 2.504097,3.576313 2.504255,3.574554 2.504361,3.572587 2.50447699999999,3.57084 2.504583,3.568731 2.504699,3.566653 2.50480999999999,3.565399 2.504848,3.564418 2.50494399999999,3.562436 2.50506,3.561114 2.505138,3.559385 2.50509199999999,3.558475 2.50496699999999,3.5580

Next thing to try is to change driver from 9.06.01.00 to something else.

Best Answer

ODBC Problem

I think this is a function of the PostgreSQL ODBC driver

[HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBCINST.INI\PostgreSQL]
"MaxLongVarcharSize"="4094"

Changing that with regedit may work (no tabs or spaces around =). However, there may be other drawbacks.

This is also documented in the psqlODBC Configuration Options..

Max LongVarChar: The maximum precision of the LongVarChar type. The default is 4094 which actually means 4095 with the null terminator. You can even specify (-4) for this size, which is the odbc SQL_NO_TOTAL value.

I believe setting this to SQL_NO_TOTAL (ie., -4) means that the ODBC driver has to malloc rather static allocate the col size.

GIS sub-optimal

From the PostGIS angle, you should be using Well-Known-Binary via ST_AsBinary and not storing the WKT as nvarchar, but instead storing the geometry from WKB in SQL Server.

Related Question