Home > Uncategorized > OLE DB provider “SQLNCLI10” for linked server returned message “No transaction is active.”

OLE DB provider “SQLNCLI10” for linked server returned message “No transaction is active.”

Harnessing Linked Servers_1

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.

 

Advertisements
Categories: Uncategorized
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: