Use of Distributed Transaction Coordinator

• PHXHoward

  SSCrazy Eights

  Points: 8389

  More actions

  March 15, 2016 at 1:33 pm

  #309120

  Hi, we have a stored procedure that queries a linked server and copies some data over to the local server where the sp executes.

  When I run it using SQL Server Management Studio it runs fine... exec sp_migratetask parameter 1, parameter 2, parameter 3

  But when the Java application running through Websphere runs it, it uses DTC and fails with "No transaction is active." which I think might be a firewall issue. When I run it in SSMS, it does not use DTC and succeeds.

  If I run it like this in SSMS, it will fail in the same way.

  BEGIN DISTRIBUTED TRANSACTION

  exec sp_migratetask parameter 1, parameter 2, parameter 3

  Does anyone know if using DTC is some sort of configuration option in a Java or WAS data source?

• SeniorITGuy

  SSC Eights!

  Points: 864

  More actions

  March 15, 2016 at 4:45 pm

  #1865491

  PHXHoward,

  DTC is the Microsoft Distributed Transaction Coordinate and is used for coordinating transactions that are run between linked servers in SQL or other servers like Web services.

  This is a service that should be enabled on the SQL servers.

  You then configure security for DTC through the dcomcnfg mmc.

  Check out Irina Tudose's blog post on this:

  http://yrushka.com/index.php/sql-server/security/configure-msdtc-for-distributed-transactions/

  If you are running on a cluster then you can add MSDTC to the cluster for all active instances on the cluster nodes. Check out Allen Hirt's post on this:

  http://sqlha.com/2013/03/12/how-to-properly-configure-dtc-for-clustered-instances-of-sql-server-with-windows-server-2008-r2/

  Hope this helps!

  Jon