Linked Server with MS OLEDB Driver 19 for SQL Server (MSOLEDBSQL19)

Question

Linked Server with MS OLEDB Driver 19 for SQL Server (MSOLEDBSQL19)

christian_t

Hall of Fame

Points: 3588
More actions
October 24, 2023 at 1:52 pm

#4313980
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

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Emperor100 SSCrazy Points: 2173 More actions · Answer 1

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.

=======================================================================

christian_t Hall of Fame Points: 3588 More actions · Answer 2

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

Ken McKelvey SSCoach Points: 18856 More actions · Answer 3

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.

christian_t Hall of Fame Points: 3588 More actions · Answer 4

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?

christian_t Hall of Fame Points: 3588 More actions · Answer 5

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

This reply was modified 1 year, 1 month ago by christian_t.