| Author |
 Topic  |
|
|
sytless006
Starting Member
Philippines
2 Posts |
 Posted - 09/01/2005 : 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
|
Edited by - sytless006 on 09/01/2005 22:08:37
|
|
|
WYSIWYG
Starting Member
USA
15 Posts |
Posted - 09/13/2005 : 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
Philippines
2 Posts |
Posted - 10/04/2005 : 04:42:16
|
sorry ... didnt get to reply sooner ...
and yeah thats what i did =) .. thanks alot =) |
|
|
|
WYSIWYG
Starting Member
USA
15 Posts |
Posted - 10/04/2005 : 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 =)
|
|
|
| |
Topic |
|
MSDTC on server '' is unavailable. only in trigger
Forum Locked
