“Invalid package number range” error in AX Retail CDX

I stumbled across the above error running the P-jobs in an AX 2012 R2 installation the other day. It popped up in a test environment and to be honest we had been a bit rough to the message database; but nothing that should mess things up. The reason behind the error was probably a conflict between the current package number and a previously handled package number.

Starting the troubleshoot we quickly realise that there is no number sequence to pull the package numbers from (the package number is added to the records in the message database to identify the messages).

The quick (and dirty) solution was to manually increase the highest package number in both in- and outgoing to a number a certain amount higher. Make sure to mark the records with an error as finished, restart the service and start the distribution from AX again.

The service will now pull the next package number based on the new highest number + 1. If that does not do the trick try increasing the number even more.


Added 26th of April 2016:

Here’s a sql script in case you need to do a complete renumbering of the package numbers:

-- Add column for temporary package number
ALTER TABLE IncomingMessages ADD PackageNo2 INT NULL

-- Fill temp package number. In this case start it of by 2000000
SET @id = 2000000

UPDATE IncomingMessages
SET @id = PackageNo2 = @id + 1

-- Overwrite the package number on the outgoing packages using the old package number as reference
-- Notice: if you start the new numbering too low you can mix up the package numbers on the outgoing table
UPDATE outgoing
SET outgoing.PackageNo = incoming.PackageNo2
FROM OutgoingMessages AS outgoing
INNER JOIN IncomingMessages incoming ON outgoing.packageNo = incoming.PackageNo

-- Update the package number on the incoming messages
UPDATE IncomingMessages
SET PackageNo = PackageNo2

-- Get rid of the temporary column
ALTER TABLE dbo.IncomingMessages