Linked server with trigger error

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

Linked server with trigger error

Author

Topic

Fran_pascual
Starting Member

5 Posts

Posted - 2011-09-27 : 05:53:08

Hi all:

I'm trying to synchronize 2 databases via a linked server from a SQLServer2000 inWS2003, and 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

Kristen
Test

22859 Posts

Posted - 2011-09-27 : 06:05:36

Does this work?


INSERT INTO OPEN_LINK.MyDatabaseName.dbo.table1(q_sent, op1)
select 'text', 'new'

I twill probably work OK if there is no transaction - perhaps the transaction is nested and that's giving a problem? (but I'l clutching at straws)

Fran_pascual
Starting Member

5 Posts

Posted - 2011-09-27 : 07:43:29

I think the problem is inserting the query into the trigger code( permissions maybe?)

Kristen
Test

22859 Posts

Posted - 2011-09-27 : 08:32:29

I would have expected a more specific error message.

If you just do


BEGIN TRANSACTION
INSERT INTO OPEN_LINK.MyDatabaseName.dbo.table1(q_sent, op1)
select 'text', 'new'
ROLLBACK

i.e. without any dependency on any other code, what error do you get?

(Change the column names / values to something appropriate please)

Kristen
Test

22859 Posts

Posted - 2011-09-27 : 08:34:34

"We also tested

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

Don't remember reading that before. You need a database name in there don't you? (Or are you relying on a Default Database perhaps?)

Fran_pascual
Starting Member

5 Posts

Posted - 2011-09-27 : 09:28:06

BEGIN TRANSACTION
INSERT INTO OPEN_LINK.MyDatabaseName.dbo.table1(q_sent, op1)
select 'text', 'new'
ROLLBACK

doesnt work but

BEGIN TRANSACTION
INSERT INTO OPEN_LINK...table1(q_sent, op1)
select 'text', 'new'
ROLLBACK

its ok

I am using Postgres Linked server with PGNP obdc, but in theory this is not the problem because the connection and simple sentences are carried out correctly

tnks for your answers Kristen

Kristen
Test

22859 Posts

Posted - 2011-09-27 : 12:42:11

"I am using Postgres Linked serve"

Ah, OK. Different syntax then.

Earlier you said:

"We also tested

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

Same mistake"

The example above is using INSERT INTO ... SELECT ..., rather than INSERT INTO ... VALUES ...

I don't see why they should be different, but perhaps try that in your code?

Fran_pascual
Starting Member

5 Posts

Posted - 2011-09-28 : 02:51:38

no way :S

if i change select -> values = Incorrect syntax. the open-query syntax is always is the same.

Kristen
Test

22859 Posts

Posted - 2011-09-28 : 03:54:09

But this doesn't involve OPENQUERY

Fran_pascual
Starting Member

5 Posts

Posted - 2011-09-28 : 04:52:51

the problem with thq other sentence:

INSERT INTO OPEN_LINK.MyDatabaseName.dbo.table1(q_sent, op1)

is that with de obdc it takes the ".MyDatabaseName.dbo.table1" all as table name and if i try OPEN_LINK.table1 fails too :S

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources