Linked server update from a trigger

More
10 years 10 months ago #2686 by 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

Please Log in or Create an account to join the conversation.

More
10 years 10 months ago #2702 by 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.

Please Log in or Create an account to join the conversation.

More
10 years 10 months ago #2705 by 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
);

Please Log in or Create an account to join the conversation.

More
10 years 10 months ago #2706 by 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
);

Please Log in or Create an account to join the conversation.

More
10 years 10 months ago #2707 by Moderator
One more question: is it an UPDATE trigger? Would you send trigger definition just in case?

Thank you!

Please Log in or Create an account to join the conversation.

More
10 years 10 months ago #2708 by fran_pascual
I need insert. update, and delete, but now I'm trying with insert.
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

Please Log in or Create an account to join the conversation.

More
10 years 10 months ago #2710 by 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 ( www.pgoledb.com/index.php?option ... wnload&cid [0]=3&Itemid=68).

Please Log in or Create an account to join the conversation.

More
10 years 10 months ago #2719 by 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 ^^

Please Log in or Create an account to join the conversation.

Time to create page: 0.072 seconds
Powered by Kunena Forum