SET TRANSACTION ISOLATION LEVEL SERIALIZABLE

Biztalk 2006 Tip of the week :

When you have an error in the eventviewer like this :

The adapter "SQL" raised an error message. Details "HRESULT="0x80004005" Description="Transaction (Process ID 108) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction."
".

Put this in your storedprocedure and everything works fine again :

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE