Unable to insert data into the Channel database – String or Binary data would be truncated

Dynamics AX
2012 R2/R3 Retail

 

Description:

 

While inserting a Distribution package job like 1010 into the Channel database following message appears and the operation stops.

 

Full error message:

Microsoft.Dynamics.Retail.StoreConnect.Request.SQLHandler.ProcessSourceRequestHeaderException:
ProcessTargetRequestHeader failed due to an InvalidOperationException.

connectionString:
Data Source=743R1;Initial Catalog=POSDB;Integrated Security=True;MultipleActiveResultSets=True;Connect
Timeout=60;Encrypt=True;TrustServerCertificate=True;Application
Name=”Commerce Data Exchange Async Client” —>
System.InvalidOperationException: The given value of type String from the data source cannot be
converted to type nvarchar of the specified target column. —>
System.InvalidOperationException: String or binary data would be truncated.

   at
System.Data.SqlClient.SqlBulkCopy.ConvertValue(Object value, _SqlMetaData
metadata, Boolean isNull, Boolean& isSqlType, Boolean&
coercedToDataFeed)

   — End of inner exception stack trace —

 

 

How to troubleshoot the issue:

The message “String and Binary data would betruncated”, means that the data length of a string is longer than accepted
by the field.

 

Validate which field is longer than accepted by

  • Copy your Channel database to the same server as your AX database
  • Change the AX database name in the script attached
  • Open a new Query on the Channel database and execute the statement

 

 

 

Result:

The result shows us that the Dirparytable NameAlias has a record that is 45
characters in length in the AX database, but only 20 characters are allowed in
the channel database.

 

 

tableName

ColumnName

CHANNELLEN

HQMAXLEN

DIRPARTYTABLE

NAMEALIAS

20

45

NUMBERSEQUENCETABLE

TXT

30

35

BARCODESETUP

DESCRIPTION

30

26

RETAILTENDERTYPETABLE

NAME

30

23

RETAILSIZEGROUPTABLE

DESCRIPTION

30

22

INVENTMODELGROUP

NAME

30

16

RETAILSTYLEGROUPTABLE

DESCRIPTION

30

15

CASHDISC

DESCRIPTION

30

11

 

 

 

The solution:

  • You can change the schema in the channel database to accept the needed input
    • Retail SDK/CreateDatabase.dll if done correctly
    • Or you can rename the value in AX to only have 20 characters

 

 

 

Author: Kim
Truelsen

Date:
15/5-2015

MS_RETAIL_MAX_LENHTH_RETAIL_DIFF.sql