Error: Transaction context in use by another session

Business Central/NAV Technical Forum
1

I have a customer who has a large number of Item Vendors and Purchase Prices which are updated on a daily basis, based upon files received daily from the vendors. Instead of storing this in NAV, then we wanted to keep this out of NAV, as it otherwise would require a very large number of system updates every day. So instead we created a view (linked object) to the data in it’s SQL Server database. When using the view, then it runs fine. When using a simple function to test the new “table”, then it works fine.

But I’m getting this error:

The following SQL Server error or errors occurred when accessing the Item Vendor table:
3910,“42000”,[Microsoft][ODBC SQL Server Driver][SQL Server]Transaction context in use by another session.

whenever I’m trying to run the update order promising function’s (AvailablitityManagement CU 99000889). The code that gives the problem is located in the Requisition Line table:

IF (Type = Type::Item) AND ("No." <> '') AND ("Prod. Order No." = '') THEN BEGIN
IF ItemVend.GET("Vendor No.","No.") THEN BEGIN
"Vendor Item No." := ItemVend."Vendor Item No.";
UpdateOrderReceiptDate(ItemVend."Lead Time Calculation");
END;
GetDirectCost(FIELDNO("Vendor No."))
END;

The error occurs on the ItemVend.GET statement. And if I’m trying with the same parameters in a test codeunit, then it works fine here!

The client is running NAV 2009 R2 and SQL Server 2008.

2

Hi Erik,

Not sure if you have found the solution, but I believe that you have a few options.

  1. Enable the Distributed Transaction Coordinator (DTC) on both Servers. You will need to ensure the Windows Service is both started and enabled (Search for “Enable DTC”).

  2. Ensure that the “LinkedInTransaction” Table Property of the Linked Tables is set to NO.

  3. Define the views with the No Lock Hint in the From Clause(s) of the View (I would anticipate that this will override Navisions’ Repeatable Read Table Hint that is used when a Navision Transaction has begun).

Option 1 is the only option that ensures Data Consistency during the Navision Transaction.

To test the changes you can add the following line of code to your test scripts:
CURRENTTRANSACTIONTYPE := TRANSACTIONTYPE::Update;

Or simply Modify any Record of any other Navision Table to automatically escalate Navisions’ CURRENTTRANSACTIONTYPE.

3

Thanks I now finally had time to test it… But it didn’t work.

My guess is that the problem is that the view is first getting one of the fields from Navision, before getting the remaining data from the other database.

What do you mean with the last two lines in your answer?