Possible Bug in AX 2009 WorldShip Integration
Posted Tuesday, June 29th, 2010
We have, at a client, integrated WorldShip with AX via the export map in WorldShip writing to the ShipCarrierStaging interface table. The ShipCarrierStaging table is an existing table in AX – its part of the standard shipping integration with AX2009 – and is designed to accept any incoming freight record from a shipping package (like WorldShip). When you invoice a packing slip, it pulls the tracking number and freight charges from this table. So, this table is never populated with MorphX commands – it is always being populated via a SQL insert or via an ODBC update.
The insert trigger that intercepts the record does certain things to the inserted record, then writes it to the table. One of the things it does, at the top of the trigger, is "set @recid = select max(recid) from ShipCarrierStaging" to set a unique value for the RECID field in the table.
All of the above is standard AX.
Here’s the problem. Between when the "select max(recid)…" command issues and when it actually inserts the record can be anywhere from immediately to a second or two depending on the size of the table, the state of the server, etc. When you have multiple WorldShip stations inserting records simultaneously, it is possible for Station 1 to deliver a record to the table, then have Station2 deliver a record, then the trigger writes Station1′s record, and then the trigger writes Station2′s record. However, since the read for Station2 on the select max() happened before the Station1 write, both records get exactly the same RECID. Therefore, the Station2 record fails on a primary key violation, since the RECID is the primary key. I can easily replicate this as follows:
1. Write a simple insert statement to the table enclosed in a do while loop with a counter set to 10,000
declare @counter as int
set @counter = 1
While @counter <10000
begin
insert into Shipcarrierstaging (column name list) values (value list)
set @counter = @counter +1
end
You’ll have to edit the column name list to match the table (without the RECID field included) and the value list must be provided.
2. Copy the insert statement to another query window
3. Start the first query, go to the next window, start that one.
4. See the error in the second window.
I can solve this in SQL by changing the RECID to an identity column and letting it increment, then modifying the trigger to avoid the RECID field entirely. I haven’t yet talked to the dev team on how to modify the trigger via AX’s development environment (if we don’t, any sync to the database will overwrite the trigger modifications).
This error is a critical issue if you have more than one shipping machine. Its been reported to MS via the OTC and they have submitted it to queue, but no resolution has been promised.
Cheers,
Dwight
Tags: UPS Worldship; Shipping Integration
Posted in Development, Dynamics AX, System - AX, Trade and Logistics
Need Expert Help With Dynamics AX?
Get the support you need to effectively manage your business.
Learn more about our Dynamics AX support options
[...] A couple of posts came out of it that you can find at the IBIS Dynamics Care site or here and here. Categories: General Comments Tags: Dynamics AX; Dynamics GP Comments (0) [...]
Thanks for finding that. We are just getting ready to do the integration and that would have killed us. Might even be as easy as creating a shadow table (ShipCarrierStagingShadow) that has the indetity column in it and just use that as the recid inside the trigger. you can also put more audit logging into the shadow table to help diagnose any issues.
In case others come acrosss this article. This issue seems to be addressed in KB2292919,
“ShipCarrierStagingInsTrig trigger created on the ShipCarrierStaging table when called simultaneously by two clients will result in a primary key violation to one of the callers”,
which is included in Hotfix Rollup 6.