Linked server update from a trigger

[fran_pascual]

Hi all:

I'm trying to synchronize 2 databases via a linked server from a SQL SERVER 2000 sp4 in Microsoft Server 2003 r2 sp2 to Postgresql8.4.7 in ubuntu 10, and by interface of SQLserver work perfectly, the problem is I need that when an insert is performed on the database A to automatically update the database B.

This function is performed via triggers, the problem is that if I put the query of the linkedserver in the trigger gives me the following error:

Server: Msg 8522, Level 18, State 1, Line 1
Distributed transaction aborted by MSDTC.

The MSDTC service is enabled with all the options allowed.

the query is like:

INSERT INTO OPENQUERY (OPEN_LINK, 'select q_sent, op1 from table1') select 'text', 'new';

We also tested

insert into [OPEN_LINK]...table1 (q_sent, op1) values ('text', 'new');

Same mistake

Any idea plz?



Tnks

[Moderator]

Do the INSERT statements work from SSMS?

Is "Allow inprocess" option selected in PGNP Provider properties (in SSMS)?

Please send DDL schema of table1.

[fran_pascual]

Tnks for your answer

If i only run the sentence it works, the problem is when i add it to trigger .
And "Allow in process" option is checked in PGNP Provider properties.



CREATE TABLE table1
(
code serial NOT NULL,
q_sent character varying(800),
estado character varying(50)
)
WITH (
OIDS=FALSE
);

[fran_pascual]

Tnks for your answer

If i only run the sentence it works, the problem is when i add it to trigger .
And "Allow in process" option is checked in PGNP Provider properties.



CREATE TABLE table1
(
code serial NOT NULL,
q_sent character varying(800),
estado character varying(50)
)
WITH (
OIDS=FALSE
);

[Moderator]

One more question: is it an UPDATE trigger? Would you send trigger definition just in case?

Thank you!

[fran_pascual]

I need insert. update, and delete, but now I'm trying with insert.

Code: Select all

use trigger_test;
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TRIGGER trigger_origin_table_link
ON dbo.origin_table
AFTER INSERT
AS
insert into OPEN_LINK...table1 (q_sent, estado)
select 'text', 'new'
GO


The final trigger have more code but this test one doesnt work so first i am trying to work it first.

tnks

[Moderator]

Have you enabled "two phase commit protocol" (2PC)? The test works on a system with 2PC support enabled.

Please see description of 2PC in Developer's Manual on pp 34, 35 (http://www.pgoledb.com/index.php?option ... wnload&cid[0]=3&Itemid=68).

[fran_pascual]

Finally!!!! the problem was with:

2.6.3 Enabling prepared transactions in PostgreSQL

Some later versions of PostgreSQL have prepared transactions disabled by default. To enable the prepared
transactions, edit postgresql.conf file as described below. Open the postgresql.conf file in editor and find line with
max_prepared_transactions parameter (if missing, new line can be added). Uncomment the line by removing ‘#’
symbol in front and set the parameter equal to maximum allowed number of connections or more, e.g.
max_prepared_transactions = 100;
# zero disables the feature

Restart PostgreSQL Server.

tnks a lot ^^