distributed transaction

Hi everyone,
I've 2 Windows 2000 server running each own instance of SQL2000. I've setup
both linked servers @. both end.
At server A, it'll call a sp in server B, whereby this sp will update server
B tables based on server A's data. And the server A table A will trigger ba
ck to server B.
Server_A call ServerB.sp
|
V
store procedure @. serverB
insert into serverB.dbB.dbo.tableB
select * from serverA.dbA.dbo.tableA where key=X
|
V
serverB.dbB.dbo.tableB trigger back to serverA
if record not found in serverA
insert into serverA
else
update into server A
However I encountered error stating:
Server: Msg 7391, Level 16, State 1, Procedure sp_update_across_linked_serve
r, Line 112
The operation could not be performed because the OLE DB provider 'SQLOLEDB'
was unable to begin a distributed transaction.
OLE DB error trace [OLE/DB Provider 'SQLOLEDB' ITransactionJoin::JoinTransac
tion returned 0x8004d00a].
[OLE/DB provider returned message: New transaction cannot enlist in the spec
ified transaction coordinator. ]
I found out if I drop the trigger, the sp will perform correctly. But the pr
oblem is I need to keep the trigger.
Can someone enlighten me on this ?
thanks in advance
KristeHi
http://groups-beta.google.com/group...=UTF-8&oe=UTF-8
Regards
Mike
"Kriste L" wrote:

> Hi everyone,
> I've 2 Windows 2000 server running each own instance of SQL2000. I've setu
p both linked servers @. both end.
> At server A, it'll call a sp in server B, whereby this sp will update serv
er B tables based on server A's data. And the server A table A will trigger
back to server B.
> Server_A call ServerB.sp
> |
> V
> store procedure @. serverB
> insert into serverB.dbB.dbo.tableB
> select * from serverA.dbA.dbo.tableA where key=X
> |
> V
> serverB.dbB.dbo.tableB trigger back to serverA
> if record not found in serverA
> insert into serverA
> else
> update into server A
>
> However I encountered error stating:
> Server: Msg 7391, Level 16, State 1, Procedure sp_update_across_linked_ser
ver, Line 112
> The operation could not be performed because the OLE DB provider 'SQLOLEDB
' was unable to begin a distributed transaction.
> OLE DB error trace [OLE/DB Provider 'SQLOLEDB' ITransactionJoin::JoinTrans
action returned 0x8004d00a].
> [OLE/DB provider returned message: New transaction cannot enlist in the sp
ecified transaction coordinator. ]
> I found out if I drop the trigger, the sp will perform correctly. But the
problem is I need to keep the trigger.
> Can someone enlighten me on this ?
> thanks in advance
> Kriste
>|||I've checked all the necessary things,
- use the dtcping, the 2 servers' rpc server and reverse binding are ok in
both direction.
- there's no firewall in both servers
- in the sp and trigger, SET XACT_ABORT ON and SET REMOTE_PROC_TRANSACTIONS
OFF
in my situation, is the trigger causing a loopback operations?
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
news:75A495AD-7C8F-4B82-8E0C-C72C68609339@.microsoft.com...
> Hi
>
http://groups-beta.google.com/group...=UTF-8&oe=UTF-8
> Regards
> Mike
> "Kriste L" wrote:
>
setup both linked servers @. both end.
server B tables based on server A's data. And the server A table A will
trigger back to server B.
sp_update_across_linked_server, Line 112
'SQLOLEDB' was unable to begin a distributed transaction.
ITransactionJoin::JoinTransaction returned 0x8004d00a].
specified transaction coordinator. ]
the problem is I need to keep the trigger.