We've recently installed the newest Microsoft OLE DB Driver for SQL Server (msoledbsql) as it now supports multi-subnet failover capabilities (detailed here: - https://blogs.msdn.microsoft.com/sqlnativeclient/2018/03/30/released-microsoft-ole-db-driver-for-sql-server/)
However we are seeing an issue with queries using linked servers.
When we run a simple query through linked server "test": -
select * from test.master.sys.databases;
The error returned is: -
Msg 8522, Level 16, State 3, Line 1 Microsoft Distributed Transaction Coordinator (MS DTC) has stopped this transaction.
I've checked the settings for MSDTC on both servers (source and target) and they seem to be correct.
I've seen online that this can be fixed by explicitly opening a distributed transaction:-
BEGIN DISTRIBUTED TRAN select * from test.master.sys.databases; ROLLBACK
But that errors out with: -
Msg 3910, Level 16, State 2, Line 1 Transaction context in use by another session.
Has anyone seen this before? Any help or advice would be greatly appreciated.