October 24, 2023 at 1:52 pm
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
October 24, 2023 at 9:30 pm
October 25, 2023 at 8:35 am
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
October 25, 2023 at 1:56 pm
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?
November 15, 2023 at 2:36 pm
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 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply