Nested Triggers and Distributed Transac tion

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

Transact-SQL (2000)

Nested Triggers and Distributed Transac tion

Author

Topic

karuna
Aged Yak Warrior

582 Posts

Posted - 2006-02-16 : 22:50:12

I have 2 servers with 4 tables in all.
Server A has 2 tables and Server B has 2 tables.


Server A        Server B
Tbl1            Tbl2
Tbl3            Tbl4

I insert triggers on all 4 tables, which has to insert into other tables as per the flow here ServerA.Tbl1->ServerB.Tbl2->TServerA.Tbl3
I'm getting error stating Cannot enlist distributed transaction. MSDN says to enable Network DTC access, which I did still I get the same error.

Inside my trigger, all I have is a insert statement specifying the four part name. Both servers are made linked servers to each other.

The following flow works with out any problem.
Tbl1->Tbl2->Tbl4. Even if I put @@Nestlevel in If condition still it doesnt work.

Thanks

Karunakaran

mallier
Starting Member

24 Posts

Posted - 2006-02-17 : 04:38:08

have started MSDTC service in both server? what sql version are u using? what is the OS of both server?.There are some security check u need to do if u r using window 2003 or window xp sp2 version.

check this link for furthur information.

http://www.sql-server-performance.com/forum/topic.asp?TOPIC_ID=3451

cheers,
http://mallier.blogspot.com

karuna
Aged Yak Warrior

582 Posts

Posted - 2006-02-17 : 07:27:08

Other than Step 5 mentioned in that post, I did everything already.
I have to try that step and see what happens...

Thanks

Karunakaran

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources