Multiple Connections - different servers - Linked Server - unable to begin a distributed transaction

pb969

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

nescafe

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:

var connStr1 = new SqlConnectionStringBuilder(Connection.ConnectionString)
{
    DataSource = "server1",
    InitialCatalog = "db1"
}.ToString();

using (var ctx1 = new TypedDataContext(connStr1))
{
    ctx1.Table.Dump();
}

var connStr2 = new SqlConnectionStringBuilder(Connection.ConnectionString)
{
    DataSource = "server2",
    InitialCatalog = "db2"
}.ToString();

using (var ctx2 = new TypedDataContext(connStr2))
{
    ctx2.Table.Dump();
}

JoeAlbahari

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