Trigger not working in linked server

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)

Trigger not working in linked server

Author

Topic

shanly
Starting Member

17 Posts

Posted - 2005-08-23 : 02:12:36

I tried to link two sql server 2000 editions, MS SQL Server 2000 standard edition on windows 2003 server standard edition and MS Desktop edition 2000 on windows XP OS using linked server by Microsoft OLE DB Provider, also direct SQL Server option. I configure the liked server on MS SQL Server 2000 Standard Edition. Everything worked perfect. I can see the remote server tables in MS SQL Server 2000 Standard Edition as well as I can select data from remote server,i can insert data from sqlserver 2000 standard edition to desktop edition also the stored procedure also work fine. But I need to run a trigger on MS Sqlserver 2000 Standard Editin to synchronize the data. I can create the trigger but when i tried to insert the data it shows me a error shown below..

Created trigger is...

create trigger triggername on tablename
for insert
as
set XACT_ABORT on
insert into GPCDEVS.Bis2000.bisuser.test select * from inserted
go

gives this error

Server: Msg 7391, Level 16, State 1, Procedure tt, Line 6
The operation could not be performed because the OLE DB provider
'SQLOLEDB' was unable to begin a distributed transaction.
[OLE/DB provider returned message: New transaction cannot enlist in the
specified transaction coordinator. ]
OLE DB error trace [OLE/DB Provider 'SQLOLEDB'
ITransactionJoin::JoinTransaction returned 0x8004d00a].

THE MSDTC services is running on both servers. Also done the configuration on w2K3 server said in article 306212?

please help me in this. I am stuck with this..

eyechart
Master Smack Fu Yak Hacker

3575 Posts

Posted - 2005-08-23 : 03:29:15

have a look at this article http://support.microsoft.com/default.aspx?scid=kb;en-us;329332

-ec

Kristen
Test

22859 Posts

Posted - 2005-08-23 : 03:29:45

Is this different to the question you asked here:

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=53906

?

Kristen

eyechart
Master Smack Fu Yak Hacker

3575 Posts

Posted - 2005-08-23 : 03:34:29

quote:
Originally posted by Kristen

Is this different to the question you asked here:

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=53906

?

Kristen

Looks like it is the same thing. Although I think the reason it works using OPENQUERY in QA is that he is not firing off a distributed transaction. Using a trigger he is.

-ec

shanly
Starting Member

17 Posts

Posted - 2005-08-23 : 03:36:18

I done the configuration as said in this article.

http://support.microsoft.com/default.aspx?scid=kb;en-us;329332
but no use

I can select,insert data to & from both servers, but if i run the trigger it gives this error

thanks for the reply

shanly
Starting Member

17 Posts

Posted - 2005-08-23 : 03:41:06

i tried using begin distributed tran transaction name & commint tran transaction name too. no use still shows the same error

I same question I asked 1 week back but no one give me a proper answer. i search in google find many guys facing this problem, every
where there is no proper answer

thanks shanly

eyechart
Master Smack Fu Yak Hacker

3575 Posts

Posted - 2005-08-23 : 03:42:33

quote:
Originally posted by shanly

I done the configuration as said in this article.

http://support.microsoft.com/default.aspx?scid=kb;en-us;329332
but no use

I can select,insert data to & from both servers, but if i run the trigger it gives this error

thanks for the reply

ok, try this

http://support.microsoft.com/kb/839279

-ec

eyechart
Master Smack Fu Yak Hacker

3575 Posts

Posted - 2005-08-23 : 03:44:50

Here is the same article but for WinXP http://support.microsoft.com/?kbid=873160

-ec

shanly
Starting Member

17 Posts

Posted - 2005-08-23 : 03:58:19

yes i got it. thank you very much for your help. the problem is also related to sqlserver 2000 service pack

eyechart
Master Smack Fu Yak Hacker

3575 Posts

Posted - 2005-08-23 : 03:59:34

quote:
Originally posted by shanly

yes i got it. thank you very much for your help. the problem is also related to sqlserver 2000 service pack

If you can elaborate on that it might help someone who searches and finds this message. thanks.

-ec

shanly
Starting Member

17 Posts

Posted - 2005-08-23 : 04:34:12

on windows XP configure using this article
http://support.microsoft.com/kb/839279
on windows 2003 server configure using this article
http://support.microsoft.com/default.aspx?scid=kb;en-us;329332
also the sqlserver 2000 service pack should be same and above sp2

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources