MSDTC on server '' is unavailable. only in trigger

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

Old Forums

CLOSED - General SQL Server

MSDTC on server '' is unavailable. only in trigger

Author

Topic

sytless006
Starting Member

2 Posts

Posted - 2005-09-01 : 21:50:55

sorry if this question has already been answered but i cant seem to search the forums ...

I have this weird problem ... i have to db servers on different computers, they're aready linked.

Using sql query analyzer i can select and update data in SERVER2
using direct command eg.

--works :: CALLED FROM SERVER1 in query analyzer

select * from [SERVER2].[DBASE].[OWNER].[TABLENAME]
update [SERVER2].[DBASE].[OWNER].[TABLENAME]
set [column] = value
where [condition(s)]

-- the problem

if i put the statement in a trigger the
message MSDTC on server '' (NOTE there is no servername in the quotes) is unavailable.
or any statement the accesses the different sever cause the error to be raised

WYSIWYG
Starting Member

15 Posts

Posted - 2005-09-13 : 17:11:09

On the server where the trigger resides, you need to turn the MSDTC service on. You can this by clicking START > SETTINGS > CONTROL PANEL > ADMINISTRATIVE TOOLS > SERVICES. Find the service called 'Distributed Transaction Coordinator' and RIGHT CLICK (on it and select) > Start.

Test the trigger and see if it works. If it still does not work, wrap you trigger in the following transaction code (found below in bold):

SET XACT_ABORT ON
BEGIN DISTRIBUTED TRANSACTION

-- Put all queries in here (SELECT, INSERT, UPDATE, and DELETE)
select * from [SERVER2].[DBASE].[OWNER].[TABLENAME]
update [SERVER2].[DBASE].[OWNER].[TABLENAME]
set [column] = value
where [condition(s)]

COMMIT TRANSACTION
SET XACT_ABORT OFF

This solution solved our problems, I hope they solve yours.

We ran into the following errors, and the above fixed them:
1. MSDTC on server is unavailable.
2. Server: Msg 7395, Level 16, State 2, Procedure name, Line 26
Unable to start a nested transaction for OLE DB provider 'SQLOLEDB'. A nested transaction was required because the XACT_ABORT option was set to OFF.
[OLE/DB provider returned message: Cannot start more transactions on this session.]
OLE DB error trace [OLE/DB Provider 'SQLOLEDB' ITransactionLocal::StartTransaction returned 0x8004d013: ISOLEVEL=4096].

sytless006
Starting Member

2 Posts

Posted - 2005-10-04 : 04:42:16

sorry ... didnt get to reply sooner ...
and yeah thats what i did =) .. thanks alot =)

WYSIWYG
Starting Member

15 Posts

Posted - 2005-10-04 : 08:34:10

Glad it worked for you, I hope this helps out others also.

quote:
Originally posted by sytless006

sorry ... didnt get to reply sooner ...
and yeah thats what i did =) .. thanks alot =)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources