Linked Server with MS OLEDB Driver 19 for SQL Server (MSOLEDBSQL19)
-
Hi,
I am preparing my system for SQL Server 2022.
Therefore I try to move all the OLEDB connections to the new MSOLEDBSQL19 driver.
We have some user SPs that are using multiple linked server.
With the v18 driver I have no issue, when switching the linked server to the v19 I get flowing error message:
Microsoft Distributed Transaction Coordinator (MS DTC) has stopped this transaction.
DTC is completely deactivated in sp_configure and in the linked servers
EXEC master.dbo.sp_serveroption @server=N'<linked_server_name>', @optname=N'remote proc transaction promotion', @optvalue=N'false'
Where is this coming from and how can I fix this?
Cheers,
Chris
-
You have to make sure the linked server options are enabled to True. This will ensure and convert the local transactions to global transactions when you call a procedure or query using the linked server.
=======================================================================
-
Hi,
I tried this option but it does not change the behaviour. I tried also the option from the driver settings, however I am just doing a SELECT in the procedure not an update:
EXEC master.dbo.sp_MSset_oledb_prop N'MSOLEDBSQL19', N'NonTransactedUpdates', 1
It must be a behaviour change in the default configuration of the driver as the same linked server using the 18.6.3 driver works as intended.
Chris
- christian_t wrote:
I am just doing a SELECT in the procedure not an update
If you are using four part naming try using OPENQUERY instead.
-
Thank you Ken, this would be a mitigation of the problem.
As it is currently only used in stored procedure, is there a hint (SET something), so that I do not have to rewrite all the queries?
-
My current solution is to switch the Linked Server to ODBC instead of OLEDB
EXEC master.dbo.sp_addlinkedserver @server = N'<linked_server_name', @srvproduct=N'', @provider=N'MSDASQL', @provstr=N'Driver={ODBC Driver 18 for SQL Server};Server=<server_name>;Trusted_Connection=yes;TrustServerCertificate=yes;Encrypt=yes;'
However a real solution to prevent OLEDB from starting a MSDTC would be nice
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply