I am getting where my linked server open query statement is failing with the following error:
Code: 7412 SQL State: 01000 --- OLE DB provider "PGNP" for linked server "DDS_PRD_LOAD" returned message "ERROR: DECLARE CURSOR may only be used in transaction blocks
I am getting this error only sometimes, most of the time it works without error. The query is like:
insert into ddm_prd.dbo.agg_dmm select * from OPENQUERY(DDS_PRD_LOAD, 'select * from whse.agg_dmm where create_ts > ''1970-01-01 00:00:00.00000''::timestamp')
This SQL is run via EXEC command.
My fetch size is set to 100000 due to the fact that on many tables I am pulling large (over 1 million rows) of data to refresh my SQLServer reporting mart.
My source database is Greenplum version 4.0.5 (PG version 8.2)
Please help. I am just looking for how to make this connection more stable.