linked server problem with error message 7391

I have two computers both running Windows Server 2003 and SQL Server 2000. I
have a trigger on a table located on the first computer which includes a line
of code that inserts into a table on the second computer. The two computers
are set up as linked servers (without loopback).
The code used to work so that when you insert into the table of the first
computer, the trigger ran properly and inserted into the second computer.
However, after I installed SP3 for SQL Server 2000 to fix another bug, the
trigger stopped working properly. Every time i try to run the code, it
generates this error message:
Server: Msg 7391, Level 16, State 1, Procedure UserSongAddition, Line 20
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].
I have not touched any of the code, so something in terms of the setup must
have changed so that it doesn't work anymore, how ever I have no idea what is
wrong.
After reading the many threads about this I have checked the following items:
1) DTC is running properly on both computers
2) Setup within the component services with Network DTC Access and
NetworkService for log on Account are all correct
3) I call SET XACT_ABORT ON before the insert statement
4) The servers are not on a cluster
5) The server names are correct based on checking with @.@.servername
6) The servers can talk to each other by name and not just IP Addresses.
7) The line of code that fails in the trigger works properly when it is run
on the second computer
8) The line of code that fails in the trigger also works properly when
copied and pasted into a stored procedure of the first computer and run over
the linked server
9) Lastly, the line of code is an insert call, but when I change it to a
select call within the trigger for testing, it also produces the same error.
So essentially I can run the code as a stored procedure but not as a trigger.
If you need any other information, please let me know. Please help me fix
this problem. Thank you.
See if any of these links help. It may be a problem with MS DTC being
disabled in WIN 2003 by default.
http://support.microsoft.com/default...b;en-us;817064
http://support.microsoft.com/?id=827805
http://support.microsoft.com/default.aspx?kbid=329332
"Zwyatt" wrote:

> I have two computers both running Windows Server 2003 and SQL Server 2000. I
> have a trigger on a table located on the first computer which includes a line
> of code that inserts into a table on the second computer. The two computers
> are set up as linked servers (without loopback).
> The code used to work so that when you insert into the table of the first
> computer, the trigger ran properly and inserted into the second computer.
> However, after I installed SP3 for SQL Server 2000 to fix another bug, the
> trigger stopped working properly. Every time i try to run the code, it
> generates this error message:
> Server: Msg 7391, Level 16, State 1, Procedure UserSongAddition, Line 20
> 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].
> I have not touched any of the code, so something in terms of the setup must
> have changed so that it doesn't work anymore, how ever I have no idea what is
> wrong.
> After reading the many threads about this I have checked the following items:
> 1) DTC is running properly on both computers
> 2) Setup within the component services with Network DTC Access and
> NetworkService for log on Account are all correct
> 3) I call SET XACT_ABORT ON before the insert statement
> 4) The servers are not on a cluster
> 5) The server names are correct based on checking with @.@.servername
> 6) The servers can talk to each other by name and not just IP Addresses.
> 7) The line of code that fails in the trigger works properly when it is run
> on the second computer
> 8) The line of code that fails in the trigger also works properly when
> copied and pasted into a stored procedure of the first computer and run over
> the linked server
> 9) Lastly, the line of code is an insert call, but when I change it to a
> select call within the trigger for testing, it also produces the same error.
> So essentially I can run the code as a stored procedure but not as a trigger.
> If you need any other information, please let me know. Please help me fix
> this problem. Thank you.