MSDTC - SQL Server Forums

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 2012 Forums

SQL Server Administration (2012)

MSDTC

Author

Topic

wided
Posting Yak Master

218 Posts

Posted - 2014-06-12 : 10:01:23

hello

i have 2 servers: server01 et server02 (SRBDRH7\EXIST)

I insert data on server02 from the trigger

what about this message?
MSDTC is not available on the serveur02(SRBDRH7\EXIST)

it worked well before the servers stop

it can be a service that has stopped, which?

thanks

wided
Posting Yak Master

218 Posts

Posted - 2014-06-12 : 10:03:18

the problem on SQL 2005

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2014-06-12 : 10:27:16

http://stackoverflow.com/questions/633805/msdtc-service-on-sql-server-2005

Too old to Rock'n'Roll too young to die.

wided
Posting Yak Master

218 Posts

Posted - 2014-06-12 : 12:54:42

it is a windows service?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-06-12 : 13:07:27

You should not be using a distributed transaction inside a trigger as it participates in the transaction. If the insert on the second server fails, the whole transaction is rolled back on server 1. Is that what you want?

MSDTC is a Windows service.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

wided
Posting Yak Master

218 Posts

Posted - 2014-06-13 : 05:15:12

yes tkizer

wided
Posting Yak Master

218 Posts

Posted - 2014-06-13 : 06:40:24

I have checked the servers are linked and MSDTC is enabled.

I found something else,
the query works fine if I run it from Query Analyzer, but i have the message if I call the trigger

wided
Posting Yak Master

218 Posts

Posted - 2014-06-13 : 06:46:14

are there any services related to the trigger?

wided
Posting Yak Master

218 Posts

Posted - 2014-06-13 : 06:48:22

trgger server to server?

wided
Posting Yak Master

218 Posts

Posted - 2014-06-13 : 07:23:07

the query select .. from server2 works from

but , insert et update do not work

wided
Posting Yak Master

218 Posts

Posted - 2014-06-13 : 08:14:47

the query select .. from server2 works from TRIGGER

but , insert et update do not work

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-06-13 : 11:41:12

I would recommend using transactional replication for this instead of a trigger.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2014-06-13 : 12:08:37

You would not need a transaction for the SELECT; only for the INSERT or UPDATE (or DELETE). Check this link to see if you have MSDTC set up properly on both servers: http://www.sqlvillage.com/Articles/Distributed%20Transaction%20Issue%20for%20Linked%20Server%20in%20SQL%20Server%202008.asp

HTH

Too often we enjoy the comfort of opinion without the discomfort of thought. - John F. Kennedy

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources