Mssql-docker: Enable MSDTC distributed transactions on SQL Server on Linux

[laurentleseigneur]

on windows sqlserver, we need to copy some .DLL and run sql commands to add extra stored procedures.

how does it work with linux docker image ?

thanks

[twright-msft]

Are you trying to do an XA with Oracle or something else? Is the .DLL a .NET assembly or something else? How do you call it?

[mi-hol]

@laurentleseigneur did you see the question?

[laurentleseigneur]

hi,

when using sql server with XA transaction on a windows box, i need to configure extra sp using specific sqljdbc_xa.dll, which is packaged in jdbc driver distribution under enu\xa\x64\
my question is how to create thoses stored procedures ( dbo.xp.sqljdbc_xa_.*) with linux docker image ? is there any documentation about that ?
thanks,

L.

[twright-msft]

@laurentleseigneur - Based on some reading about the JDBC XA implementation it looks like it depends on MSDTC. As of right now, we don't have MSDTC running on Linux. So that would be a blocker even if you could get the stored procedures created. Further, it looks like those are extended stored procedures based on the naming convention of xp_* and I dont think those will work on SQL on Linux right now either. I'm going to rename this issue to be more of a feature request to enable MSDTC distributed transactions in SQL Server on Linux in general. Once we have that, making things work in a container context should be pretty straightforward.

[TiloGit]

see here for further details about XA.
And what is possible on Linux: https://blogs.msdn.microsoft.com/bobsql/2018/01/28/how-it-works-sql-server-dtc-msdtc-and-xa-transactions/

[kapilth]

MSDTC is now available on SQL on Linux (in SQL Server 2019 preview)
https://docs.microsoft.com/en-us/sql/linux/sql-server-linux-configure-msdtc?view=sql-server-ver15

[kapilth]

in SQL Server 2019 preview you can use XA transaction on SQL by executing this SP's
Install sqljdbc XA. -
exec sp_sqljdbc_xa_install

To uninstall
exec sp_sqljdbc_xa_uninstall

After executing the sp_sqljdbc_xa_install you can use XA transaction on SQL on Linux.

[brynjellis]

Hi Guys, I think I have a similar question to what was originally asked and that is with the traditional Windows approach you could run a command like:

_DECLARE @P1 INT;
DECLARE @P2 NCHAR;
DECLARE @P4 BINARY;
EXEC master..xp_sqljdbc_xa_recover @P1 OUTPUT, @P2 OUTPUT, 25165824, @P4 OUTPUT;
SELECT @P1, @P2, @P4;_

And it would give you some output and a decent enough clue as to whether you'd configured XA correctly.

However, you run that against an instance on Linux and you get the message below because obviously Linux doesn't use DLLs.

_Msg 17750, Level 16, State 0, Procedure master..xp_sqljdbc_xa_recover, Line 1
Could not load the DLL SQLJDBC_XA.dll, or one of the DLLs it references. Reason: 126(The specified module could not be found.)._

So how does it work with Linux? What SP's are used in JDBC calls etc? The current documentation is not clear on this and I don't know where to go next.

[PiotrKlimczak]

I've got it working after executing "exec sp_sqljdbc_xa_install" on master db as per previous post.

[brynjellis]

Ah, cool, thanks, I misread @kapilth comment about exec sp_sqljdbc_xa_install. I skipped over it thinking it was the usual xa install script used when setting up in Windows.

Executing that has now made it work.

[kapilth]

Sql 17 cu16 now adds support for msdtc on SQL On Linux

https://techcommunity.microsoft.com/t5/SQL-Server/Introducing-Distributed-transaction-functionality-on-SQL-Server/ba-p/786632

[mariav77]

Hi,
We are using the linux version MSSQL 2017 CU16.
We tried the TestXA example (https://docs.microsoft.com/en-us/sql/connect/jdbc/understanding-xa-transactions?view=sql-server-2017) to make a simple XA INSERT command and we got the following error:
Exception in thread "main" javax.transaction.xa.XAException: com.microsoft.sqlserver.jdbc.SQLServerException: Failed to create the XA control connection. Error: "Procedure expects parameter '@errormegssage' of type 'nchar'."
What kind of bug is this? This is a show stopper for Linux installations.

[kapilth]

Hi,
We are using the linux version MSSQL 2017 CU16.
We tried the TestXA example (https://docs.microsoft.com/en-us/sql/connect/jdbc/understanding-xa-transactions?view=sql-server-2017) to make a simple XA INSERT command and we got the following error:
Exception in thread "main" javax.transaction.xa.XAException: com.microsoft.sqlserver.jdbc.SQLServerException: Failed to create the XA control connection. Error: "Procedure expects parameter '@errormegssage' of type 'nchar'."
What kind of bug is this? This is a show stopper for Linux installations.

This exception means that the parameter expected is of type nchar.
Can you reproduce this on SQL Server 2019 preview

[mariav77]

Sql Server 2019 preview works fine by using the same script.
The issue is with Sql Server 2017.
It seems like a bug. Do you have any quick fix or workaround for 2017 to make it work?
as the 2019 preview version is not for production.
Thanks in advance

[kapilth]

Sql Server 2019 preview works fine by using the same script.
The issue is with Sql Server 2017.
It seems like a bug. Do you have any quick fix or workaround for 2017 to make it work?
as the 2019 preview version is not for production.
Thanks in advance
I have investigated and see that there is a bug in the parameter passing.
What is your time timeline and how big is your deployment.

[mariav77]

"I have investigated and see that there is a bug in the parameter passing.
What is your time timeline and how big is your deployment."
This is a blocker on the transition to a new hosting provider that supports only Linux OS.
Really appreciate your prompt reply.

[kapilth]

"I have investigated and see that there is a bug in the parameter passing.
What is your time timeline and how big is your deployment."
This is a blocker on the transition to a new hosting provider that supports only Linux OS.
Really appreciate your prompt reply.

The fix is planned for next CU update.

[mariav77]

Thanks Kapil, When do you think the next CU will take place?

[kapilth]

Thanks Kapil, When do you think the next CU will take place?
I do not have exact date but looking at this list it should be between 2 and 3 months from previous CU.
https://support.microsoft.com/en-us/help/4047329