1

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?
Dijkgraaf
  • 11,049
  • 17
  • 42
  • 54
Mario
  • 978
  • 2
  • 11
  • 31

1 Answers1

2

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.

zurebe-pieter
  • 3,246
  • 21
  • 38
  • 1
    Isn't the transactionIsolationLevel setting only available on a Receive Location, and only by adding the sqlAdapterInboundTransactionBehavior Behaviour? https://msdn.microsoft.com/en-us/library/dd788041.aspx – Dijkgraaf Jul 20 '17 at 00:07
  • Correct, for a receive location that is the only available way, including the setting for "ambient transactions". I was convinced that this was about a send port, therefore I did not mention this, so very good point made! – zurebe-pieter Jul 20 '17 at 09:02
  • Good explanation. So we have SQL Server 2014 with AlwaysOn. So my cross-database procedures are not supported with transactions, correct? – Mario Jul 20 '17 at 11:15
  • @Mario I have not tested this before, but according to the documentation I read, they are NOT. Here is a very good explanation why https://blogs.msdn.microsoft.com/alwaysonpro/2014/01/06/not-supported-ags-with-dtccross-database-transactions/ – zurebe-pieter Jul 21 '17 at 19:25