Problem with distributed transaction via linked server

Problem with distributed transaction via linked server

Post by Q2loYX » Thu, 28 Oct 2004 19:15:04


Hi,

Could someone please help me with the following problem.
I've been busy with this one for about 2 weeks and getting desperate now.

I want to do a distributed transaction via a linked server.
This is the query:

SET XACT_ABORT ON
BEGIN DISTRIBUTED TRANSACTION
update table1
set name = 'MachineA'
where id = 1

insert into [MACHINEB\TEST].cihatdb.dbo.table2
values (2, 'MachineB')
COMMIT
GO

This is the result:

(1 row(s) affected)

Server: Msg 7391, Level 16, State 1, Line 7
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].

My Configuration:

Machine A:
Windows 2003 Enterprise Edition
SQL Server 2000 Enterprise edition version 8.00.0818 (SP3A + KB815495)
MDAC 2.8 + MS04-003
DTC is enabled on this server as described in
http://www.yqcomputer.com/
IP Adress: 192.168.2.10 255.255.0.0
No firewall, no domain
Name resolving trough DNS Server

Machine B:
Windows 2003 Enterprise Edition
SQL Server 2000 Enterprise edition version 8.00.0818 (SP3A + KB815495)
MDAC 2.8 + MS04-003
DTC is enabled on this server as described in
http://www.yqcomputer.com/
IP Adress: 192.168.2.11 255.255.0.0
No firewall, no domain
Name resolving trough DNS Server

Regards,

Cihat
DBA
 
 
 

1. problem with distributed transaction via linked server

2. Problem with executing Distributed Transactions on Linked Server

I have a Linked Server i.e. my SQL Server 2000 is Linked to the Oracle
Database.
The Link is perfect and dont have any problems in accessing the data
from ORACLE. The only problem i am facing is when i am trying
to initiate a Distributed transaction. A simple Insert Query is
working fine but when i try to perform the same through a Stored
Procedure having the Begin Transaction i am getting the error
mentioned below.
Server: Msg 7391, Level 16, State 1, Procedure InsertGFSTemp, Line 12
The operation could not be performed because the OLE DB provider
'MSDAORA' was unable to begin a distributed transaction.
OLE DB error trace [OLE/DB Provider 'MSDAORA'
ITransactionJoin::JoinTransaction returned 0x8004d01b].
I have tried all the help available and have all the supported
softwares installed as well on the SQL Server machine. Still facing
this problem.
If anybody could help me with the same.

3. Problem with executing Distributed Transactions on Linked Servers

4. Error while performing a Distributed Transaction on Linked Server

5. SSAS, linked server, unwanted distributed transaction

6. Distributed transaction on linked server.

7. Linked Server and unable to begin a distributed transaction error

8. Distributed Transaction on Linked Servers

9. loopback linked server on a distributed transaction

10. Linked servers and distributed transactions question

11. Linked Oracle server - Unable to begin distributed transaction error

12. Informix Linked Server Distributed Transaction

13. SQL 2000 - Distributed Transactions and Linked Servers

14. Distributed transactions with linked SQL Servers.

15. Loopback linked servers and distributed transactions