[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Distributed Transaction /Linkservers

Posted on 2007-08-04
8
Medium Priority
?
359 Views
Last Modified: 2013-11-05
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
0
Comment
Question by:bobdba
  • 4
  • 2
6 Comments
 

Author Comment

by:bobdba
ID: 19631678
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
 
LVL 10

Expert Comment

by:lahousden
ID: 19632361
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
 

Author Comment

by:bobdba
ID: 19632390
lahousden,
i will try your solution
0
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 

Author Comment

by:bobdba
ID: 19663156

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
 

Author Comment

by:bobdba
ID: 19669694
Any other solution for Distributed Transaction?
Please help me
0
 
LVL 10

Accepted Solution

by:
lahousden earned 2000 total points
ID: 19675764
0

Featured Post

Upgrade your Question Security!

Add Premium security features to your question to ensure its privacy or anonymity. Learn more about your ability to control Question Security today.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as wel…
Viewers will learn how the fundamental information of how to create a table.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Suggested Courses

834 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question