insert into LINKED POSTGRES from MSSQL causes 'positioned_load in pos_newload failed'

Question

I'm trying to make a sync between MSSQL database with PostGIS. There are in MSSQL views with coords, projection and other data I need. I'm trying to insert into PostGIS view and to create geometry and transform data in view's rule.

I created all needed stuff, but when inserting from MSSQL (same for using insert into linked server or insert into openquery) it throws me the following error:

OLE DB provider "MSDASQL" for linked server "PG_SYNC_TEST" returned message "positioned_load in pos_newload failed".

Msg 7343, Level 16, State 2, Line 1 The OLE DB provider "MSDASQL" for linked server "PG_SYNC_TEST" could not INSERT INTO table "[MSDASQL]".

Does anyone knows what is happening and what is set up wrong?

Also, MSSQL is 2016 and PostgreSQL 9.6.

UPDATE - Linked server definition

/****** Object: LinkedServer [PG_SYNC_TEST] Script Date: 4/28/2017 8:06:39 AM ******/ EXEC master.dbo.sp_addlinkedserver @server = N'PG_SYNC_TEST', @srvproduct=N'PostgreSQL', @provider=N'MSDASQL', @datasrc=N'pg_sync_test' /* For security reasons the linked server remote logins password is changed with ######## */ EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'PG_SYNC_TEST',@useself=N'False',@locallogin=NULL,@rmtuser=N'mssql_user',@rmtpassword='########'

GO

EXEC master.dbo.sp_serveroption @server=N'PG_SYNC_TEST', @optname=N'collation compatible', @optvalue=N'false' GO

EXEC master.dbo.sp_serveroption @server=N'PG_SYNC_TEST', @optname=N'data access', @optvalue=N'true' GO

EXEC master.dbo.sp_serveroption @server=N'PG_SYNC_TEST', @optname=N'dist', @optvalue=N'true' GO

EXEC master.dbo.sp_serveroption @server=N'PG_SYNC_TEST', @optname=N'pub', @optvalue=N'true' GO

EXEC master.dbo.sp_serveroption @server=N'PG_SYNC_TEST', @optname=N'rpc', @optvalue=N'true' GO

EXEC master.dbo.sp_serveroption @server=N'PG_SYNC_TEST', @optname=N'rpc out', @optvalue=N'true' GO

EXEC master.dbo.sp_serveroption @server=N'PG_SYNC_TEST', @optname=N'sub', @optvalue=N'true' GO

EXEC master.dbo.sp_serveroption @server=N'PG_SYNC_TEST', @optname=N'connect timeout', @optvalue=N'0' GO

EXEC master.dbo.sp_serveroption @server=N'PG_SYNC_TEST', @optname=N'collation name', @optvalue=null GO

EXEC master.dbo.sp_serveroption @server=N'PG_SYNC_TEST', @optname=N'lazy schema validation', @optvalue=N'false' GO

EXEC master.dbo.sp_serveroption @server=N'PG_SYNC_TEST', @optname=N'query timeout', @optvalue=N'0' GO

EXEC master.dbo.sp_serveroption @server=N'PG_SYNC_TEST', @optname=N'use remote collation', @optvalue=N'true' GO

EXEC master.dbo.sp_serveroption @server=N'PG_SYNC_TEST', @optname=N'remote proc transaction promotion', @optvalue=N'true' GO

Datasource (ODBC x64)

UPDATE 2 - Query that returns the error:

INSERT INTO PG_SYNC_TEST.sync.[public].v_test_points
(lat, lng, projection, elevation, geochem)
SELECT lat, lng, projection, elevation, geochem
FROM geochem

Answer 1

Check if any trigger is enabled for PG_SYNC_TEST.sync & [public].v_test_points - if yes, disable and retry.