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
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.