OLE DB provider “SQLNCLI10” for linked server returned message “No transaction is active.”
When you set up a linked server environment between two SQL servers, that are not on the same network, and you try to run a transaction that includes a reference to a linked server; by running a SQL statement like
BEGIN TRANSACTION
update LINKED_SERVER.MY_DATABASE.dbo.MY_TABLE set MY_COLUMN = 1
COMMIT TRANSACTION
and you get an error message like
OLE DB provider “SQLNCLI10” for linked server “myserver” returned message “No transaction is active.”.
Msg 7391, Level 16, State 2, Line 2
The operation could not be performed because OLE DB provider “SQLNCLI10” for linked server “myserver” was unable to begin a distributed transaction.
Then the solution turned out to be – after two days of head-scratching – to : put the Net BIOS name (computer name) for each server involved in the transaction into the hosts file at both sides of the transaction.
The hosts file is in c:\Windows\System32\Drivers\etc\hosts – and you’ll need administrator access to change it.