I'm using the DIXF entity for opening balance to import data for a ledger journal into Dynamics AX. To generate the voucher and journal number, I use the Auto-generated checkbox in the source to staging mapping. Sometimes when importing the staging data, this will cause the following error messages:
Cannot execute the required database operation. The SQL database has issued an error.
SQL error description: [Microsoft][SQL Server Native Client 10.0][SQL Server]Error converting data type nvarchar to bigint.
SQL statement: UPDATE DMFLEDGERJOURNALENTITY SET JOURNALNUM = [dbo].FN_FMT_NUMBERSEQUENCE(N'006-######',JOURNALNUM,159,0) WHERE DEFINITIONGROUP = N'AnlagenAbschreibungAX3.0_006' AND EXECUTIONID = N'AnlagenAbschreibungAX3.0_006_006-1'
Issue exist in generate staging data
'82' 'Anfangssaldo' record(s) inserted in staging
The error is caused by method generateAutoNumbers in class DMFGenerateSSISPackage, but if I put a breakpoint there and try to debug the problem, the error does not occur and after it does not happen again. I have not been able to figure out a reliable way to reproduce the problem, but it usually comes up after I created a new processing group.
I would like to know why this SQL statement sometimes fails to convert the datatypes and what I can to do prevent the error.
More by accident I stumbled upon the answer today. The problem only occurs for continuous number sequences that have free numbers in their status list and those numbers are not enough for the staging records. I guess the number sequence numbers provided for staging records have to be continuous. This also explains why the problem cannot be easily replicated because after the first import of staging data the free numbers from the status list have been used and the next import is then successfull because it only uses new numbers.
So to prevent this problem, check the continuous number sequences involved if they have free numbers in their status list.