1

Background - Main server is Microsoft SQL Server 2019 and linked server is PostgreSQL 15.

Main reason for the linking is the duplication of records.

All other DML operations are good from SQL Server to Postgres.

Enable Promotion of Distributed Transaction is False.

(EXEC master.dbo.sp_serveroption @server=..., @optname=N'remote proc transaction promotion', @optvalue=N'false')

But all operations in SQL Server trigger throw error:

OLE DB provider "MSDASQL" for linked server "POSTGRESQL" was unable to begin a distributed transaction

...although promotion is set to false.

Why does SQL Server continue to promote this transaction despite the option?

Improve this question
5
  • Are you only calling a remote stored procedure or are you using other DML?
    – Sean Gallardy
    Commented Apr 5, 2023 at 13:45
  • In the trigger I use only an insert - insert into postgres ... select ... from inserted;
    – gobsek2 Altsgamer
    Commented Apr 5, 2023 at 15:20
  • That's not an remote procedure. I'd suggest you fix your DTC setup instead of trying to bypass it.
    – Sean Gallardy
    Commented Apr 5, 2023 at 15:28
  • thx. But if I use a remote postgres procedure instead of the insert - will I be able to bypass distributed transaction?
    – gobsek2 Altsgamer
    Commented Apr 6, 2023 at 13:07
  • Depends, since this is executed inside a trigger (which is already a poor idea) then most likely not.
    – Sean Gallardy
    Commented Apr 6, 2023 at 13:27

1 Answer 1

Reset to default
0

I'm in the same boat except with a MariaDB server.

Short Answer:

  1. Triggers happen inside an implicit transaction
  2. Any time you reference remote resources inside a transaction it forces that transaction to become distributed

Long Answer:

After hours of research, even though everything I've read states that SQL Server, the ODBC driver, and DTC support XA transactions, I think it's just straight-up false for any scenario where you're not doing MSSQL - MSSQL communication.

I assume that, because of this limitation/assumption that you're only communicating MSSQL - MSSQL, it defaults to an OLE-style distributed transaction and will not translate it to an XA transaction unless it can get confirmation from the remote DTC instance.

Because of this (more specifically this comment and the one below it), I think the biggest issue is that you need DTC on both machines... however DTC is not cross-platform. Beyond it not being cross-platform, I'm not sure if DTC would even work for a non-MSSQL DBMS as it's heavily tied to SQL Server.

As an extra cherry-on-top, this also states that there is no explicit way to even force-promote a transaction to an XA transaction in TSQL.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.