When running a query using linked server connection, with
the linked server target being in a different domain, you might encounter this
issue
When the below query is run, the query would just complete
successfully.
select top 10 * from [server\instance].master.sys.all_views with (nolock)
But when you embed the query in a distributed transaction,
it throws an error message:
BEGIN DISTRIBUTED TRANSACTION;
select top 10 * from [server\instance].master.sys.all_views with (nolock)
COMMIT TRANSACTION;
Below is the error message:
OLE DB provider "SQLNCLI11" for linked
server "server\instance" returned message "No transaction
is active.".
Msg 7391, Level 16, State 2, Line 2
The operation could not be performed
because OLE DB provider "SQLNCLI11" for linked server "sf-rel-sql-03\h5rel04"
was unable to begin a distributed transaction.
In my case, the source SQL Instance (where the query is
being run) is a clustered Instance that runs on top of two physical nodes. The
destination server is in a different domain. Because the Instance is clustered,
the destination server is unable to resolve the MSDTC successfully. We have two
options to resolve this issue:
1. Cluster
the MSDTC resource
2. Add
the physical nodes names to the hosts file in the below folder (on the server that is being referred to in the linked server connection)
c:\windows\system32\drivers\etc\hosts
