Solved

DISTRIBUTED TRANSACTION HELP

Posted on 2003-11-02
4
546 Views
Last Modified: 2008-02-01
I try to use disrtibuted transaction. First,I use MSDASQL successfull:

set XACT_ABORT on
set ANSI_NULL_DFLT_ON on
set ANSI_WARNINGS on
BEGIN DISTRIBUTED TRANSACTION
select * from OPENDATASOURCE('MSDASQL','DRIVER={SQL Server};SERVER=192.168.2.1;UID=sa;PWD=PASSWORD;').pubs.dbo.jobs
commit tran

Then I try to use SQLOLEDB, But failure:

set XACT_ABORT on
set ANSI_NULL_DFLT_ON on
set ANSI_WARNINGS on
BEGIN DISTRIBUTED TRANSACTION
select * from OPENDATASOURCE('SQLOLEDB','Data Source=192.168.2.1;User ID=sa;Password=PASSWORD').pubs.dbo.jobs
commit tran

I get the following error message:
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].



but I run the code use SQLOLEDB without DISTRIBUTED TRANSACTION success:

select * from OPENDATASOURCE('SQLOLEDB','Data Source=192.168.2.1;User ID=sa;Password=PASSWORD').pubs.dbo.jobs


WHY? Please help me!
0
Comment
Question by:tomcom
  • 2
4 Comments
 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 125 total points
ID: 9665751
Check out this article from MSDN:
FIX: MS DTC Limited to 16 Nodes in Single Distributed Transaction
http://support.microsoft.com/default.aspx?scid=kb;en-us;295086

Anthony
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 10141694
Do you need any more help with this question?
0

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Join & Write a Comment

Suggested Solutions

In this article—a derivative of my DaytaBase.org blog post (http://daytabase.org/2011/06/18/what-week-is-it/)—I will explore a few different perspectives on which week today's date falls within using Microsoft SQL Server. First, to frame this stu…
Performance is the key factor for any successful data integration project, knowing the type of transformation that you’re using is the first step on optimizing the SSIS flow performance, by utilizing the correct transformation or the design alternat…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

747 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

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now