Share via

Do ODBC Linked Servers (MariaDB) support XA Transactions?

Evan Howlett 0 Reputation points
2024-05-03T20:31:26.51+00:00

My setup involves 3 servers, 2 MariaDB servers -- one under my control, the other not -- and an MSSQL server. I have the two MariaDB servers linked via the Spider Engine and I have my internal MariaDB server set up as a linked server in Sql Server with the 64-bit ODBC driver.

I have a table in SQL Server that, when modified, I want a trigger to propagate those changes through to the linked, internal MariaDB server which (through its spider engine) then sends the changes off to the external MariaDB server.

Everything I've read states that MariaDB and the Spider engine, ODBC, SQL Server, and DTC all support XA Trasactions. BUT, I cannot for the life of me get DTC to use XA transactions for the linked, internal server, however.

Every time I go to make a change to the table in Sql Server, I get:

OLE DB provider "MSDASQL" for linked server "{servername}" returned message "[ma-3.1.20]Optional feature not implemented".

This "optional feature not implemented" message makes me believe SQL Server is trying to initiate a normal distributed transaction instead of using an XA transaction because MariaDB doesn't support SQL Server-style distributed transactions.DTC Settings

User's image

Firewall settings

User's image

Some suggestions stated to turn this off, I've tested it with both True and False values.

User's image

Trigger definition:

CREATE TRIGGER trigger_newRecord ON db.schema.table
AFTER INSERT
as
    insert into linkedServer.db..table ( ... ) 
    select ...
    from inserted

Am I missing something or is it not possible?

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,864 questions

1 answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. LucyChenMSFT-4874 1,280 Reputation points
    2024-05-06T05:42:03.84+00:00

    Hi @Evan Howlett,

    Thanks for your information.

    I've checked the steps of how to configure XA transaction for SQL Server. I think maybe this is not a driver issue.

    The error is often be seen when an application tries to set connection / statement attributes with a value > 1000 (Upon calling SQLSetConnectOption, SQLSetStmtOption, SQLSetConnectAttr, SQLSetStmtAttr, SQLGetConnectOption, SQLGetStmtOption, SQLGetConnectAttr, SQLGetStmtAttr)

    Hope this can help you well.

    Best regards,

    Lucy Chen

    If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

    Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

    https://docs.microsoft.com/en-us/answers/support/email-notifications

    0 comments