I'm glad one of our biztalk devs is very cooperative today 😉
Scenarion 1 : add
begin tran ... commit tran
Just encapsulating the trigger with its own Begin tran ... commit tran, off course didn't do the trick.
Because: that tran is just an augmentation of the @@trancount which - because of the "set implicit transactions on" >0 at that time.
When I added a while loop :crazy:
while @@trancount > 0
resulted in a failure of the biztalk apps.
set transaction isolation level read committed
IMO this one would be plausible, but once again, it resulted in messages like
Process ID 61 attempted to unlock a resource it does not own: DATABASE: 7 Retry the transaction, because this error may be caused by a timing condition. If the problem persists, contact the database administrator.
and sqlserver got itself messed up once again.