We ended up using connections with DTC turned off as we were running out of time but we now have a reply from OT which may help others -
..." The good news is that I think that I have found a workaround for this issue. If I turn Read Committed Snapshot on the SQL Server Primary Database then I can use your amended solution (Metastorm default connection) and when I press the Add button new data is inserted into the PCZ_EntityAssignment table. I've verified that this workaround works with both Oracle and SQL Server linked secondary databases.
We have already tested MBPM with Read Committed Snapshot for SQL Server and recommend that customers running with multiple Event Manager Threads and SQL Server have Read Committed Snapshot switched on. Here is some information:
Read Committed Snapshot
We have found that using Read Committed Snapshot during testing and on customer sites has greatly reduced the number of deadlocks that happen at runtime. This also means that we don't need to use no lock to reduce deadlocks because they are no longer occurring. It's very important to ensure that the tempdb database size is large enough when using Read Committed Snapshot especially on very busy systems.
These links provide more information:
Row Versioning Resource Usage:
"Row versioning-based isolation levels reduce the number of locks acquired by transaction by eliminating the use of shared locks on read operations. This increases system performance by reducing the resources used to manage locks. Performance is also increased by reducing the number of times a transaction is blocked by locks acquired by other transactions.
Row versioning-based isolation levels increase the resources needed by data modifications. Enabling these options causes all data modifications for the database to be versioned. A copy of the data before modification is stored in tempdb even when there are no active transactions using row versioning-based isolation. The data after modification includes a pointer to the versioned data stored in tempdb. For large objects, only part of the object that changed is copied to tempdb."
Troubleshooting Insufficient Disk Space in tempdb:
The following statement turns on read committed snapshot:
ALTER DATABASE <MetastormDB> SET READ_COMMITTED_SNAPSHOT ON;
Note that all database connections have to be closed for this statement to run successfully.
To verify that the database is running with this setting:
SELECT is_read_committed_snapshot_on FROM sys.databases WHERE name= '<MetastormDB>'
This query will return 1 when READ_COMMITTED_SNAPSHOT is switched on and 0 when it is switched off.
Note that using Read Committed Snapshot ensures that SQL Server uses a similar locking mechanism to Oracle.