2

Dears,

I have scenarios where I have to perform updates on the intrinsic database of LightSwitch and call some SQL stored procedures in the save pipeline all in ONE TRANSACTION, such that if an error happened in the LS save pipeline then my stored procedure calls are rolled back.

The recommended way of doing this is to set up an ambient transaction in the SaveChanges_Executing event, and to dispose of it in the SaveChanges_Executed and SaveChanges_ExecuteFailed events. As described in this articles http://www.codemag.com/Article/1103071

But this has two fatal problems:

  1. It does not work when I publish the app on Azure since distributed transactions are not supported there.
  2. Also it throws an error when I try to save changes to ApplicationData sources using the ServerApplicationContext. The error is this: The underlying provider failed on EnlistTransaction

Has anyone found a cleaner way to handle transactions in LightSwitch that works both on Azure and through ServerApplicationContext??

Thanks a lot

Ahmad Akra
  • 505
  • 4
  • 14

1 Answers1

2

Currently, distributed transactions using MSDTC do not work against SQL Azure. They will work just fine against SQL Server in a VM running in Azure, however. MSDTC is tied to running on a domain controller, generally, and that doesn't make sense in the public cloud context. An alternative DTC is likely needed, but this is not something that has been publicly announced as of today.

I don't think Lightswitch is the main issue here (though perhaps it has some additional issue beyond what I have described).

I hope that at least explains why it doesn't work today - I wish I had a better answer for you, but right now it is not possible. The "workarounds" being used are to build applications that can be resilient to the commits happening on each side (or not) and recovering from the failed cases.

Conor Cunningham MSFT
  • 4,151
  • 1
  • 15
  • 21