Posted by Erland Sommarskog on 02/01/07 23:05

rbg (rbg.net@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, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Back to original message]