"jeff_y" <jeffrey***@abc.com> wrote in message
news:OgufZO8KIHA.5360@TK2MSFTNGP03.phx.gbl...
> Dear gurus,
> Your help is greatly appreciated...
> My environment:
> SQL Server 2K5 EE SP2 build# 3154 on Windows 2K3 SP1
>
> I have three servers:
> Server_1, Server_2, and Server_3
>
> My issues:
> I have the issue between Server_1 and Server_2 when I start a distributed
> transaction (all linked server are set up properly), like:
> on Server_1:
>
> begin distributed tran
> select * from Server_2.master.sys.databases
> commit tran
>
> I will receive the error:
> OLE DB provider "SQLNCLI" for linked server "Server_2" returned message
> "No transaction is active.".
>
> Msg 7391, Level 16, State 2, Line 2
> The operation could not be performed because OLE DB provider "SQLNCLI" for
> linked server "Server_2" was unable to begin a distributed transaction.
>
> This happens on Server_2, when I run the following:
>
> begin distributed tran
> select * from Server_1.master.sys.databases
> commit tran
>
> However if I run the query on Server_3 or run against Server_3 from either
> Server_1 or Server_2, no error occurs:
> i.e if I run on Server_3:
> begin distributed tran
> select * from Server_1.master.sys.databases
> select * from Server_2.master.sys.databases
> commit tran
>
> or run on either Server_1 or Server_2
> begin distributed tran
> select * from Server_3.master.sys.databases
> commit tran
>
> they are all successful.
> What puzzles me is the fact:
> Server_1 and Server_3 can have distributed transactions without problem,
> and so can Server2 and Server_3, but NOT Server_1 and Server_2.
>
> So this seems something is wrong between Server_1 and Server_2, also I can
> confirm all servers are in the same domain and MS DTC is running on all
> servers fine.
>
>
> can some shed some lights on how come this can happen?
> Thanks in advance for your time.
>
> Kind regards,
> Jeff
>