Use of Distributed Transaction Coordinator

  • 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?

  • 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

Viewing 2 posts - 1 through 1 (of 1 total)

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