VERIFIED SOLUTION i

Resolve error “Transaction context in use by another session" in Portrait Dialogue


 

Issue

The following error occurs in the Portrait Dialogue (PD) logs when sampling a selection or viewing participants using a SQL Server database:

Exception: Transaction context in use by another session
Time: 23.06.2015 15:33:32
Module: C:\PROGRA~2\PST\MILLIO~1\DIALOG~1\MHDIAL~1.DLL
Version: 6.1.0.343
Filedate: 03.02.2015 13:10:24
Instancename: xxxx
Username: xxxx
ProcessID: 5744
ThreadID: 5216
Exceptiontype: EOleException
Unit: Data.Win.ADODB.pas
Procedure: Data.Win.ADODB.TADOConnection.DoConnect
Line: 1677
SQLStatement:
 
[02EF4907] Data.Win.ADODB.TADOConnection.DoConnect (Line 1675, "Data.Win.ADODB.pas")
[02EF4907] Data.Win.ADODB.TADOConnection.DoConnect (Line 1675, "Data.Win.ADODB.pas")
[02C7EE89] Data.DB.TCustomConnection.SetConnected
[0392982A] MHSQLAccessLayer.TMHSQLAccessLayer.CreateNewDefaultConnection (Line 511, "MHSQLAccessLayer.pas")
[0392989B] MHSQLAccessLayer.TMHSQLAccessLayer.GetADOConnection (Line 518, "MHSQLAccessLayer.pas")
[03929B3A] MHSQLAccessLayer.TMHSQLAccessLayer.RetrieveDatasetRaw (Line 550, "MHSQLAccessLayer.pas")
[03932C0D] MHSQLRepository.TMHSQLRepository.DoGetSQLDefFromDB (Line 288, "MHSQLRepository.pas")
[03934332] MHSQLRepository.TMHSQLRepository.InternalGetSQLDef (Line 539, "MHSQLRepository.pas")
[039344A4] MHSQLRepository.TMHSQLRepository.GetSQLDefByID (Line 574, "MHSQLRepository.pas")
[0371D1F4] MHCustomerData.TMHCustomerData.GetLookupSourceDataset (Line 717, "MHCustomerData.pas")
[0371C219] MHCustomerData.TMHCustomerData.GetLookupDataValue (Line 370, "MHCustomerData.pas")
[0371BF63] MHCustomerData.TMHCustomerData.GetDataFieldValue (Line 335, "MHCustomerData.pas")
[037255A0] MHCustomerContainer.TMHBaseContainer.RecursivelySetFieldValues (Line 1158, "MHCustomerContainer.pas")
[03723381] MHCustomerContainer.TMHBaseContainer.InternalBuildDataset (Line 698, "MHCustomerContainer.pas")
[0372313C] MHCustomerContainer.TMHBaseContainer.GetDataset (Line 622, "MHCustomerContainer.pas")
[03AE3745] MHInternalClientSupport.TMHInternalClientSupport.GetSelectionSample (Line 4014, "MHInternalClientSupport.pas")
[02C9F09E] System.Win.ComObj.TAutoObject.Invoke
 

Cause

Regarding the «Transaction in use by another session» error; We have sporadically seen this in our development environment and suspect it has to do with a change that has been made to the cursor type for SQL Server, Portrait Dialogue now use a “Forward Only” cursor instead of a “Keyset” cursor in some circumstances, for instance when retrieving dialog participants. This was done to improve performance.
 
It has been noted that the problem seems to go away when enabling MARS (Multiple Active Result Sets) for the OLE DB connection. This can be done by setting the “MARS Connection” property to true when editing the OLE DB connection in Dialogue Admin.

2016.06.01 UPDATE
A more recent approach was to modify an index on the DLG_PARTICIPANT table adding DP_INSERTED_TIMESTAMP and FILLFACTOR = 100 (see script below)

Resolution

UPDATED: April 17, 2017
The first suggestion is to install the latest Hotfix for Portrait Dialogue v6.1.

The second Suggestion, if that does not solve the issue, then try setting the MARS Connection setting to True in the Data Link Properties in Dialogue Admin.
Remember to recycle the PD COM+ package after changing the connection string.
 
User-added image

The third suggestion, if none of the above solves the problem, then review the domain sub-groups to make sure that one-to-one groups are not returning one-to-many results.
This also appears to be a symptom of duplicate records appearing in the cursor results due to incorrect setting of the 1-1 and 1-many sub-domain groups.