0

I have a linked SQL server (the two servers are on two separate continents and connected via VPN). When running the following sql, it returns the data without issue:

Select TOP 1 * from [LinkedServer].DB.DBO.Table1

When running it in a transaction, I get the error below:

BEGIN DISTRIBUTED TRANSACTION 
Select TOP 1 * from [LinkedServer].DB.DBO.Table1 
COMMIT TRANSACTION

OLE DB provider "SQLNCLI11" for linked server "LinkedServer" 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 "LinkedServer" was unable to begin a distributed transaction.

There are many posts online around this particular error message (links below) but none of the steps have solved the issue for me and I'm at a loss at this point (surely it is a firewall issue/blocked ports).

Any suggestions would be more than welcome at this point.

http://thesmartpanda.com/sqlncli11-linked-server-dtc-issue/ http://msdn.microsoft.com/en-us/library/aa561924.aspx etc

ricky89
  • 1,326
  • 6
  • 24
  • 37

1 Answers1

0

After alot of Digging around and troubleshooting the problem turned out to be DNS related. I didn't know that DTC requires the hostname rather than the IP to establish the link. Once I knew the above I tried to ping the Linked server by name and it failed.

I added the mapping in the Hosts file in the directory 'c:\windows\system32\drivers\etc'. This then allowed me to ping the server by Name as well as create a new linked server by name.

After this it all worked fine.

ricky89
  • 1,326
  • 6
  • 24
  • 37