Linked server update from a trigger

Using PGNP with MS SQL Server, Data Transformation Services (DTS), SQL Server Integration Services (SSIS), Linked Servers, Snapshot and Transactional Replication and more.

Linked server update from a trigger

Postby fran_pascual » Wed Sep 28, 2011 12:44 am

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
fran_pascual
 
Posts: 5
Joined: Wed Sep 28, 2011 12:15 am

Re: Linked server update from a trigger

Postby Moderator » Wed Sep 28, 2011 5:31 pm

Do the INSERT statements work from SSMS?

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

Please send DDL schema of table1.
User avatar
Moderator
Site Admin
 
Posts: 279
Joined: Wed Oct 29, 2008 11:27 pm

Re: Linked server update from a trigger

Postby fran_pascual » Thu Sep 29, 2011 12:13 am

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
 
Posts: 5
Joined: Wed Sep 28, 2011 12:15 am

Re: Linked server update from a trigger

Postby fran_pascual » Thu Sep 29, 2011 12:13 am

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
 
Posts: 5
Joined: Wed Sep 28, 2011 12:15 am

Re: Linked server update from a trigger

Postby Moderator » Thu Sep 29, 2011 12:22 am

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

Thank you!
User avatar
Moderator
Site Admin
 
Posts: 279
Joined: Wed Oct 29, 2008 11:27 pm

Re: Linked server update from a trigger

Postby fran_pascual » Thu Sep 29, 2011 12:39 am

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
fran_pascual
 
Posts: 5
Joined: Wed Sep 28, 2011 12:15 am

Re: Linked server update from a trigger

Postby Moderator » Thu Sep 29, 2011 8:55 am

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).
User avatar
Moderator
Site Admin
 
Posts: 279
Joined: Wed Oct 29, 2008 11:27 pm

Re: Linked server update from a trigger

Postby fran_pascual » Fri Sep 30, 2011 6:26 am

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 ^^
fran_pascual
 
Posts: 5
Joined: Wed Sep 28, 2011 12:15 am


Return to SQL Server/DTS/SSIS/Linked servers/Replication

Who is online

Users browsing this forum: No registered users and 1 guest

cron