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
bobdbaAsked:
Who is Participating?
 
lahousdenCommented:
0
 
bobdbaAuthor Commented:
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].
0
 
lahousdenCommented:
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...
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
bobdbaAuthor Commented:
lahousden,
i will try your solution
0
 
bobdbaAuthor Commented:

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
0
 
bobdbaAuthor Commented:
Any other solution for Distributed Transaction?
Please help me
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.