Call a Stored Procedure Without Wrapping in a Transaction

NonPlayerCharacter

I have a process that calls a stored procedure to copy data between two SQL databases, one being on-premise while the other is in Azure. The Distributed Transaction Coordinator (DTC) service is only available on the managed instance in Azure. Therefore, we are unable to set up the DTC in windows on both ends. SQL cannot run the stored procedure when run from Decisions as it tries to put a transaction on the stored procedure called. Because the query is across servers, SQL tries to escalate the transaction to make it transactionally secure on both servers. This cannot be done as Azure SQL cannot do use DTC service so the procedure fails.

Is there a way we can call a Stored Procedure without wrapping the call in a transaction using an Internal Service Method? This will prevent us from getting distributed transaction issues on procedures that query across servers.

Nathan_Decisions

This can be accomplished using the Raw SQL step. Under Connection Settings there is an option to toggle Use Transaction Data which prevents the execution of a transaction.

If you are in a newer version of Decisions, 8.18.0.72809 or newer, Decisions has also added a new feature called "Run Without Transaction" to disable creating a transaction when executing stored procedures when transactions are not used.