Multiple Connections - different servers - Linked Server - unable to begin a distributed transaction
Hi,
I'm using multiple connections in Linqpad to read and write to the same DB on different servers.
Ordinarily, Linqpad only supports multiple connections to different databases on the same server. Using a Linked Server gets around this issue.
I've setup the Linked Server in SQL Server Management Studio, and I can happy interact with the MSSQL server as a "Linked Server"
I've also setup the DB connection in Linqpad and added the "linked server" using the "include additional databases" section on of the Linqpad Connection dialog.
However, when I try to write with the linked server, I get this error....
SqlException: The operation could not be performed because OLE DB provider "MSOLEDBSQL" for linked server "SERVERNAME.COM" was unable to begin a distributed transaction.
OLE DB provider "MSOLEDBSQL" for linked server "SERVERNAME.COM" returned message "The partner transaction manager has disabled its support for remote/network transactions.".
Reading from the linked server works fine
Comments
As an alternative, you said "same DB" - i assume the database structure is identical between servers?
In that case you can re-use the generated Typed Data Context for different servers:
It sounds like a setup issue with the SQL Server instances.
There are a bunch of solutions posted here that could help:
https://stackoverflow.com/questions/7473508/unable-to-begin-a-distributed-transaction