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 5 (of 5 total)

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