Related to this question: Executing a stored procedure inside BEGIN/END TRANSACTION
I am calling my stored procedure via BizTalk. Because BizTalk is creating a TRANSACTION my stored procedure has no TRANSACTION handling. However, I have to call another stored procedure within the one called by BizTalk. BUT the second stored procedure is called against another database within the same MSSQL Instance.
Is MS SQL aware of this cross database procedure call and does a rollback of the second stored procedure as well?
What happens if:
- The cross db stored procedure fails?
- The outer stored procedure fails AFTER the cross db stored procedure has been called?
If you are using WCF-Custom using a sqlBinding for example, you can specify the TRANSACTION ISOLATION LEVEL in your WCF adapter configuration. Off course, you can set this to your desired state.
FYI: By default, this setting in BizTalk server is the Serializable isolation level.
In fact, your WCF adapter, when using a transaction, will start a distributed transaction and will inherently support transactions cross-database and even cross-server. Depending on what you exactly do within SQL, this may or may not be supported. Some limited linked servers via certain providers do not support it for example.
Another example is that cross-database transactions in AlwaysOn availability groups are not supported up to, and including, SQL Server 2016. (explanation: https://blogs.msdn.microsoft.com/alwaysonpro/2014/01/06/not-supported-ags-with-dtccross-database-transactions/)
It is said that this (cross-database transactions in AlwaysOn AG's) is to be supported starting from SQL Server 2017 however.
TLDR; if you are using a distributed transaction and are not using anything out of the ordinary like legacy linked server providers or AlwaysOn availability groups, this is supported and working as expected.
