Link to home
Start Free TrialLog in
Avatar of bobdba
bobdba

asked on

Distributed Transaction /Linkservers

Hi,
we have 2 Servers Server1(sql2000) and Server2(sqlserver 2005)
select * from Server1.DB1.dbo.Table1
select * from Server2.DB2.dbo.Table2
The above queries on Server1 are working fine.


--stored proc created on Server1
create  proc sproc
as
declare @col1 int
declare @var1 int, @var2 int

begin distributed tran

insert into DB1.dbo.Table1(col2)values('test')
set @col1 = @@identity

insert into Server2.DB2.dbo.Table2(col1, col2)
values(@col1, 'test')
set @var2 = @@error

if(@var1 = 0 and @var2 = 0)
     commit tran
else
     rollback tran

please help me
Avatar of bobdba
bobdba

ASKER

DTC started on both servers.i got follwong error
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].
Avatar of lahousden
Doe this relate?

http://support.microsoft.com/kb/839279/en-us

If not then you may want to query the Microsoft site for the wording of the errors you received in case your specific situation is mentioned in another post...
Avatar of bobdba

ASKER

lahousden,
i will try your solution
Avatar of bobdba

ASKER


i  modified the setting mentioned in document ( http://support.microsoft.com/kb/839279/en-us) and rebooted machine couple of times but it's not working.
please help me
Avatar of bobdba

ASKER

Any other solution for Distributed Transaction?
Please help me
ASKER CERTIFIED SOLUTION
Avatar of lahousden
lahousden
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial