Linked server update from a trigger

  • fran_pascual
  • Topic Author
  • Offline
  • User
  • User
More
12 years 6 months ago #2686 by fran_pascual
Linked server update from a trigger was created 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
12 years 6 months ago #2702 by Moderator
Replied by Moderator on topic Re: Linked server update from a trigger
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.

  • fran_pascual
  • Topic Author
  • Offline
  • User
  • User
More
12 years 6 months ago #2705 by fran_pascual
Replied by fran_pascual on topic Re: Linked server update from a trigger
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.

  • fran_pascual
  • Topic Author
  • Offline
  • User
  • User
More
12 years 6 months ago #2706 by fran_pascual
Replied by fran_pascual on topic Re: Linked server update from a trigger
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
12 years 6 months ago #2707 by Moderator
Replied by Moderator on topic Re: Linked server update from a trigger
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.

  • fran_pascual
  • Topic Author
  • Offline
  • User
  • User
More
12 years 6 months ago #2708 by fran_pascual
Replied by fran_pascual on topic Re: Linked server update from a trigger
I need insert. update, and delete, but now I'm trying with insert.
Code:
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.

Time to create page: 0.156 seconds
Powered by Kunena Forum