Why transaction gets promoted to Distributed transaction

[rbg]

Hi,
On My local SQL server I have added a linked server to another SQL
server (remoteserver) in another Windows NT Domain.

When I run this code
select count(*) from remoteserver.mosaics.dbo.Location

This works fine.

However when I use
begin transaction
select count(*) from remoteserver.mosaics.dbo.Location

It errors out saying that
The operation could not be performed because the OLE DB provider
'SQLOLEDB' was unable to begin a distributed transaction.
New transaction cannot enlist in the specified transaction
coordinator. ]
OLE DB error trace [OLE/DB Provider 'SQLOLEDB'
ITransactionJoin::JoinTransaction returned 0x8004d00a].

My question is even though I am just reading data from the
remoteserver, why does the local transaction get promoted to a
distributed transaction.
Any help will be grately appreciated.

TIA...Rohit

[joeNOSPAM]

On Feb 1, 7:30 am, "rbg" <[e-mail]:

Hi,
On My local SQL server I have added a linked server to another SQL
server (remoteserver) in another Windows NT Domain.

When I run this code
select count(*) from remoteserver.mosaics.dbo.Location

This works fine.

However when I use
begin transaction
select count(*) from remoteserver.mosaics.dbo.Location

It errors out saying that
The operation could not be performed because the OLE DB provider
'SQLOLEDB' was unable to begin a distributed transaction.
New transaction cannot enlist in the specified transaction
coordinator. ]
OLE DB error trace [OLE/DB Provider 'SQLOLEDB'
ITransactionJoin::JoinTransaction returned 0x8004d00a].

My question is even though I am just reading data from the
remoteserver, why does the local transaction get promoted to a
distributed transaction.
Any help will be grately appreciated.

TIA...Rohit

It is because you have a transaction established when you do the
query. The
other DBMS has to establish it's part of the transaction. This step
comes
before the DBMS does anything with the SQL, even before parsing it. I
bet the same would happen if you tried a remote 'select 1'.

Joe Weinstein at BEA Systems

[Erland Sommarskog]

rbg (rb*****@gmail.com) writes:

On My local SQL server I have added a linked server to another SQL
server (remoteserver) in another Windows NT Domain.

When I run this code
select count(*) from remoteserver.mosaics.dbo.Location

This works fine.

However when I use
begin transaction
select count(*) from remoteserver.mosaics.dbo.Location

It errors out saying that
The operation could not be performed because the OLE DB provider
'SQLOLEDB' was unable to begin a distributed transaction.
New transaction cannot enlist in the specified transaction
coordinator. ]
OLE DB error trace [OLE/DB Provider 'SQLOLEDB'
ITransactionJoin::JoinTransaction returned 0x8004d00a].

My question is even though I am just reading data from the
remoteserver, why does the local transaction get promoted to a
distributed transaction.

There is a transaction, and it doesn't matter what it does on the
other end.

There are suggestions in http://support.microsoft.com/kb/873160. I
played around with it, but I was not able to get it work myself.

Beware that the KB article suggests that you should open port 135
in Windows Firewall. When you do this, use the Change scope option
to limit which computers the port is open for. Do not open it for
access from the Internet. Port 135 is a very dangerous port to
have open.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx